Search code examples
sqlmysqlimplicit-conversionexplicit-conversion

mysql datetime comparison

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.

Solution

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