MySQL 8 Administrator???s Guide

Problems with DATE columns

In MySQL, the default format of a DATE value is YYYY-MM-DD. Standard SQL does not permit any other format. This is the format that should be used in the UPDATE expressions and in the WHERE clause in a SELECT statement. The following is an example of the date format:

SELECT * FROM table_name WHERE date_col >= '2011-06-02';

When a constant string is compared to DATE, TIME, DATETIME, or TIMESTAMP using <, <=, =, >=, >, or BETWEEN operators, MySQL converts the string into an internal long integer value. MySQL does this so as to achieve a faster comparison. However, the following exceptions are applicable to this conversion:

• Comparing two columns
• Comparing a DATE, TIME, DATETIME, or TIMESTAMP column to an expression ...