GSTAR GSTAR - 1 year ago 129
SQL Question

mysql datetime comparison

For example the following query works fine:

FROM quotes
WHERE expires_at <= '2010-10-15 10:00:00';

But this is obviously performing a 'string' comparison - I was wondering if there was a function built in to MySQL that specifically does 'datetime' comparisons.

Answer Source

...this is obviously performing a 'string' comparison

No - if the date/time format matches the supported format, MySQL performs implicit conversion to convert the value to a DATETIME, based on the column it is being compared to. Same thing happens with:

WHERE int_column = '1'

...where the string value of "1" is converted to an INTeger because int_column's data type is INT, not CHAR/VARCHAR/TEXT.

If you want to explicitly convert the string to a DATETIME, the STR_TO_DATE function would be the best choice:

WHERE expires_at <= STR_TO_DATE('2010-10-15 10:00:00', '%Y-%m-%d %H:%i:%s')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download