mysql datetime comparison

Question

For example the following query works fine   SELECT      FROM quotes   WHERE expires at  lt    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

User · Answer

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

User · Answer

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

User · Answer

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  lt   STR TO DATE  2010-10-15 10 00 00     Y- m- d  H  i  s

[sql] mysql datetime comparison

Examples related to sql

Examples related to mysql

Examples related to implicit-conversion

Examples related to explicit-conversion