Date and Time Types
842
• As a 1- or 2-digit number in the range
1
to
99
. Values in the ranges
1
to
69
and
70
to
99
are
converted to
YEAR
values in the ranges
2001
to
2069
and
1970
to
1999
.
Inserting a numeric
0
has a different effect for
YEAR(2)
and
YEAR(4)
. For
YEAR(2)
, the result has
a display value of
00
and an internal value of
2000
. For
YEAR(4)
, the result has a display value
of
0000
and an internal value of
0000
. To specify zero for
YEAR(4)
and have it be interpreted as
2000
, specify it as a string
'0'
or
'00'
.
• As the result of a function that returns a value that is acceptable in a
YEAR
context, such as
NOW()
[926]
.
Illegal
YEAR
values are converted to
0000
.
See also
Section 11.1.5.8, “Two-Digit Years in Dates”
.
11.1.5.4.
YEAR(2)
Limitations and Migrating to
YEAR(4)
Although the internal range of values for
YEAR(4)
and
YEAR(2)
is the same (
1901
to
2155
, and
0000
), the display width for
YEAR(2)
makes that type inherently ambiguous because displayed values
indicate only the last two digits of the internal values. The result can be a loss of information under
certain circumstances. For this reason, consider avoiding
YEAR(2)
throughout your applications
and using
YEAR(4)
wherever you need a
YEAR
data type. This section describes problems that can
occur when using
YEAR(2)
and provides information about migrating existing
YEAR(2)
columns to
YEAR(4)
. Note that migration will become necessary at some point because support for
YEAR
data
types with display values other than 4, most notably
YEAR(2)
, is reduced as of MySQL 5.6.6 and will
be removed entirely in a future release.
YEAR(2)
Limitations
Issues with the
YEAR(2)
data type include ambiguity of displayed values, and possible loss of
information when values are dumped and reloaded or converted to strings.
• Displayed
YEAR(2)
values can be ambiguous. It is possible for up to three
YEAR(2)
values
that have different internal values to have the same displayed value, as the following example
demonstrates:
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 use
mysqldump
to dump the table created in the preceding item, the dump file represents all
y2
values using the same 2-digit representation (
12
). If you reload the table from the dump file, all
resulting rows have internal value
2012
and display value
12
, thus losing the distinctions among
them.
• Conversion of a
YEAR(2)
or
YEAR(4)
data value to string form uses the display width of the
YEAR
type. Suppose that
YEAR(2)
and
YEAR(4)
columns both contain the value
1970
. Assigning each
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
