Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 159.2Kb
Man Pages (Zip) - 263.0Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR

11.3.4 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR

This section describes problems that can occur when using the 2-digit YEAR(2) data type and provides information about converting existing YEAR(2) columns to 4-digit year-valued columns, which can be declared as YEAR with an implicit display width of 4 characters, or equivalently as YEAR(4) with an explicit display width.

Although the internal range of values for YEAR/YEAR(4) and the deprecated YEAR(2) type 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 and omit the century digits. The result can be a loss of information under certain circumstances. For this reason, avoid using YEAR(2) in your applications and use YEAR/YEAR(4) wherever you need a year-valued data type. Note that conversion is required 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 is removed entirely in MySQL 5.7.

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);
    Query OK, 0 rows affected, 1 warning (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 example, 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 between them.

  • Conversion of a 2-digit or 4-digit YEAR data value to string form uses the data type display width. Suppose that a YEAR(2) column and a YEAR/YEAR(4) column both contain the value 1970. Assigning each column to a string results in a value of '70' or '1970', respectively. That is, loss of information occurs for conversion from YEAR(2) to string.

  • Values outside the range from 1970 to 2069 are stored incorrectly when inserted into a YEAR(2) column in a CSV table. For example, inserting 2211 results in a display value of 11 but an internal value of 2011.

To avoid these problems, use the 4-digit YEAR or YEAR(4) data type rather than the 2-digit YEAR(2) data type. Suggestions regarding migration strategies appear later in this section.

Migrating from YEAR(2) to 4-Digit YEAR

To convert 2-digit YEAR(2) columns to 4-digit YEAR columns, use ALTER TABLE. Suppose that a table t1 has this definition:

CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');

Modify the column using ALTER TABLE as follows. Remember to include any column attributes such as NOT NULL or DEFAULT:

ALTER TABLE t1 MODIFY ycol YEAR NOT NULL DEFAULT '1970';

The ALTER TABLE statement converts the table without changing YEAR(2) values. If the server is a replication master, the ALTER TABLE statement replicates to slaves and makes the corresponding table change on each one.

One migration method should be avoided: Do not dump your data with mysqldump and reload the dump file after upgrading. That has the potential to change YEAR(2) values, as described previously.

A migration from 2-digit YEAR(2) columns to 4-digit YEAR columns should also involve examining application code for the possibility of changed behavior under conditions such as these:

  • Code that expects selecting a YEAR column to produce exactly two digits.

  • Code that does not account for different handling for inserts of numeric 0: Inserting 0 into YEAR(2) or YEAR(4) results in an internal value of 2000 or 0000, respectively.