MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
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.
See also Section 11.2.9, “2-Digit Years in Dates”.