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.
This question is related to
sql
mysql
implicit-conversion
explicit-conversion
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...
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.
Source: Stackoverflow.com