MySQL DateTime Format with c# failing

Question

HI I am writing a sample application to insert and retrive datetime format . The Insert i did was sucessfull but dont know why it fails when i query to retrieve it . .

My insert goes like this..

string SqlQuery = "INSERT into BenchMarking Values (" + i + " , 'XXXX','This is a testing','M','2010-05-05 05:06:01')";
        sqlWrapper.ExecuteNonQuery(SqlQuery);

but when i query them its not returning me anything ...

retrive query

   string sqlQuery = "select Id from BenchMarking where Datetime = '2010-05-05'";
reader = sqlWrapper.ExecuteQuery(sqlQuery);

anyone knows why this is happening?

Answer 1

If you specify 2010-05-05 as a DATETIME value, it will default to 2010-05-05 00:00:00 which is different from the record you inserted.

What you are probably looking for is DATE() which extracts the date part from a DATETIME value.

select Id from BenchMarking where DATE(Datetime) = '2010-05-05'

Answer 2

You probably need to change your query to select Id from BenchMarking where Date(Datetime_column_name) = '2010-05-05'

Answer 3

"select Id from BenchMarking where (CONVERT(VARCHAR(10), ArrivalTime, 111) = '2010/05/05')"

you need to format the date to varchar.. for formatting, u can use this..

http://www.sql-server-helper.com/tips/date-formats.aspx