Mysql date comparision (greater than equal) without leading zeros

I had to do some date comparision and return a dataset. PHP sent the current date time, with a time with non leading zeros (8:00:00) for 8 am instead of (08:00:00). The case where there were no leading zeros, was giving wrong results. Can someone explain why ?

To test, run this SELECT IF(DATE_ADD('2011-03-20', INTERVAL '08:05:00' HOUR_SECOND) >= '2011-03-开发者_开发百科20 8:00:00',"yes","No")

result: No

AND

SELECT IF(DATE_ADD('2011-03-20', INTERVAL '08:05:00' HOUR_SECOND) >= '2011-03-20 08:00:00',"yes","No")

result: Yes

shouldn't both give the result : "Yes"

Is it doing string comparison for a non leading zero ?

I think this is to do with MySQL's rather dodgy date / time handling in som ecases MySQL converts to a numeric and in other cases it uses string comparisons.

From the manual

http://dev.mysql.com/doc/refman/5.0/en/using-date.html

When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the <, <=, =, >=, >, or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparison (and also for a bit more “relaxed” string checking). However, this conversion is subject to the following exceptions:

When you compare two columns

When you compare a DATE, TIME, DATETIME, or TIMESTAMP column to an expression

When you use any other comparison method than those just listed, such as IN or STRCMP().

For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison.

Strictly speaking, you are comparing against strings (not dates) and relying on automatic casting. Try this instead:

SELECT
IF(DATE_ADD('2011-03-20', INTERVAL '08:05:00' HOUR_SECOND) >= STR_TO_DATE('2011-03-20 8:00:00', '%Y-%m-%d %H:%i:%s'), 'Yes', 'No'),
IF(DATE_ADD('2011-03-20', INTERVAL '08:05:00' HOUR_SECOND) >= STR_TO_DATE('2011-03-20 08:00:00', '%Y-%m-%d %H:%i:%s'), 'Yes', 'No')

Update:

According to the manual, DATE_ADD() can return a date or a string:

The return value depends on the arguments:

DATETIME if the first argument is a DATETIME (or TIMESTAMP) value,
or if the first argument is a DATE and the unit value uses HOURS,
MINUTES, or SECONDS.

String otherwise.

To ensure that the result is DATETIME, you can use CAST() to convert the first argument to DATETIME.

So the left operand of the >= comparison is a string thus you're getting string comparisons. Try the CAST('2011-03-21' AS DATE) suggestion.