The YEAR type is a one-byte type used for representing years. It can be declared as YEAR(4) or YEAR(2) to specify a display width of four or two characters. The default is four characters if no width is given.

YEAR(4) and YEAR(2) differ in display format, but have the same range of values. For four-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000. For two-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).

You can specify input YEAR values in a variety of formats:

Illegal YEAR values are converted to 0000.

Although YEAR(4) and YEAR(2) have the same range of values, displayed YEAR(2) values can be ambiguous due to the smaller number of displayed digits. It is possible for up to three distinct values to have the same displayed value. The following example demonstrates this, and also that the values are distinct internally.

mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t SET y4 = y2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| y2   | y4   |
+------+------+
|   12 | 1912 |
|   12 | 2012 |
|   12 | 2112 |
+------+------+
3 rows in set (0.00 sec)

If you dump the table using mysqldump, the dumped y2 values will all have the same two-digit representation (12). If you reload the table from the dump file, all rows are treated as 2012, thus losing the distinctions among them.

To avoid these problems, use YEAR(4) rather than YEAR(2), or restrict YEAR(2) values to those lying in the range from 1970 to 2069.

See also Section 11.3.7, “Two-Digit Years in Dates”.