CODEWITHSUNDEEP
c#sqldatetime
newoverstack
newoverstack

Reputation: 25

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

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?

Upvotes: 2

Views: 2029

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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");

Upvotes: 2

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

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) + "'");

Upvotes: 0

Related Questions