Working with Database Structures - Learning MySQL - page 190

DATE

Stores and displays a date in the format YYYY-MM-DD for the range 1000-01-01 to
9999-12-31. Dates must always be input as year, month, and day triples, but the
format of the input can vary, as shown in the following examples:
YYYY-MM-DD or YY-MM-DD
It's optional whether you provide two-digit or four-digit years. We strongly
recommend that you use the four-digit version to avoid confusion about the
century. In practice, if you use the two-digit version, you'll find that 70 to 99
are interpreted as 1970 to 1999, and 00 to 69 are interpreted as 2000 to 2069.
YYYY/MM/DD , YYYY:MM:DD , YY/MM/DD , or other punctuated formats
MySQL allows any punctuation characters to separate the components of a
date. We recommend using dashes and, again, avoiding the two-digit years.
YYYY-M-D , YYYY-MM-D , or YYYY-M-DD
When punctuation is used (again, any punctuation character is allowed),
single-digit days and months can be specified as such. For example, February
2, 2006, can be specified as 2006-2-2 . The two-digit year equivalent is available,
but not recommended.
YYYYMMDD or YYMMDD
Punctuation can be omitted in both date styles, but the digit sequences must
be six or eight digits in length.
You can also input a date by providing both a date and time in the formats described
later for DATETIME and TIMESTAMP , but only the date component is stored in a DATE
type column. Regardless of the input type, the storage and display type is always
YYYY-MM-DD . The zero date 0000-00-00 is allowed in all versions and can be used to
represent an unknown or dummy value. If an input date is out of range, the zero
date 0000-00-00 is stored. By default, from MySQL 5.0.2 onward, the zero date is
stored when you insert an invalid date such as 2007-02-31 . Prior to that version,
invalid dates are stored provided the month is in the range 0 to 12, and the day is
in the range 0 to 31. Consider this example:
mysql> CREATE TABLE testdate (mydate DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2007/02/0');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2007/02/1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2007/02/31');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2007/02/100');
Query OK, 1 row affected, 1 warning (0.00 sec)

With a version of MySQL older than 5.0.2, we would have: