11.2.4 The YEAR Type

The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR with an implicit display width of 4 characters, or as YEAR(4) or YEAR(2) with an explicit display width of 4 or 2 characters.

Note

The 2-digit YEAR(2) data type has certain issues that you should consider before choosing to use it. As of MySQL 5.6.6, YEAR(2) is deprecated: 2-digit YEAR(2) columns in existing tables are treated as before, but 2-digit YEAR(2) columns in new or altered tables are converted to 4-digit YEAR columns. For more information, see Section 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.

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

YEAR accepts input values in a variety of formats:

As 4-digit strings in the range '1901' to '2155'.
As 4-digit numbers in the range 1901 to 2155.
As 1- or 2-digit strings in the range '0' to '99'. MySQL converts values in the ranges '0' to '69' and '70' to '99' to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
As 1- or 2-digit numbers in the range 0 to 99. MySQL converts values in the ranges 1 to 69 and 70 to 99 to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.
Inserting a numeric 0 has different effects for YEAR/YEAR(4) and YEAR(2):
- For YEAR/YEAR(4), the result has a display value of 0000 and an internal value of 0000. To specify zero and have it be interpreted as 2000, specify it as a string '0' or '00'.
- For YEAR(2), the result has a display value of 00 and an internal value of 2000.
As the result of functions that return a value that is acceptable in YEAR context, such as NOW().

If strict SQL mode is not enabled, MySQL converts invalid YEAR values to 0000. In strict SQL mode, attempting to insert an invalid YEAR value produces an error.