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

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

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

[sql] mysql datetime comparison

Examples related to sql

Examples related to mysql

Examples related to implicit-conversion

Examples related to explicit-conversion