How to compare date string variable in C# with datetime field in sql query

Question

I have the following code,and I am trying to compare The date string with date time,but I can't get it to work.This does not output any rows.

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where convert(datetime, m_date) ='" + String.Format("{0:d/M/yyyy}", DateUsed) + "'");

Both datetimes are formatted exactly the same.Any ideas?

What happens when you run the exact query into the database yourself? — Arran
– Arran, Commented Sep 8, 2012 at 23:15
@Arran - I've run the query with the date instead of DateUsed,but i still get no results. — newoverstack
– newoverstack, Commented Sep 9, 2012 at 9:56

Sergey Kalinichenko · Accepted Answer · 2012-09-09 10:42:02Z

2

You are converting the m_date to datetime but DateUsed remains a string.

You should either compare them as strings, like this

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where m_date ='" + String.Format("{0:d/M/yyyy}", DateUsed) + "'");

or as datetimes, like this:

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where convert(datetime, m_date) = convert(datetime,'" + String.Format("{0:d/M/yyyy}", DateUsed) + "')");

EDIT: On SQL Server 2005 try this:

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where abs(datediff(day,convert(datetime, m_date), convert(datetime,'" + String.Format("{0:d/M/yyyy}", DateUsed) + "'))) = 0");

edited Sep 9, 2012 at 10:42

answered Sep 8, 2012 at 23:18

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

newoverstack Over a year ago

Thank you, @dasblinkenlight,still no results.I print out the date from database and the date from date used,and they are the same,but still no results.

Sergey Kalinichenko Over a year ago

@user1657214 Since you are using only the date portion, try converting to date rather than to datetime, like this:

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where convert(date, m_date) = convert(date,'" + String.Format("{0:d/M/yyyy}", DateUsed) + "')");

newoverstack Over a year ago

Just tried than but i get this: Type date is not a defined system type

newoverstack Over a year ago

yep,that seems to be the case.

Sergey Kalinichenko Over a year ago

@user1657214 See the edit, datediff should work for sql server 2005.

|

Nitesh Kumar · Accepted Answer · 2012-09-09 04:05:54Z

0

If m_date field is Date/DateTime type then following code will work fine.

DataTable tbl3 = dii.SelectGeneric("select * from widget_messages  where convert(varchar, m_date, 12) ='" + String.Format("{0:yyyyMMdd}", DateUsed) + "'");

answered Sep 9, 2012 at 4:05

Nitesh Kumar

1,7744 gold badges19 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to compare date string variable in C# with datetime field in sql query

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related