mysql datetime comparison

Question

For example the following query works fine:

SELECT * 
  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 1

...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')