Learning Mysql

DATE
Stores and displays a date in the format
YYYY-MM-DD
for the range 1000-01-01 to. 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 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, 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 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 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 aversion of MySQL older than 5.0.2, we would have:

Download 4.24 Mb.

Share with your friends: