mysql datetime comparison
SqlMysqlImplicit ConversionExplicit ConversionSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
>...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')
Solution 2 - Sql
> But this is obviously performing a 'string' comparison
No. The string will be automatically cast into a DATETIME value.
See 11.2. Type Conversion in Expression Evaluation.
> When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.
Solution 3 - Sql
I know its pretty old but I just encounter the problem and there is what I saw in the SQL doc :
> [For best results when using BETWEEN with date or time values,] use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
I assume it's better to use STR_TO_DATE since they took the time to make a function just for that and also the fact that i found this in the BETWEEN doc...