MySQL YEAR Data Type and Two-Digit Year Rules

In MySQL, YEAR data types are used to store year values.

YEAR occupies 1 byte and stores years from 1901 through 2155, plus the special value 0000.

Columns defined as YEAR data types can accept a variety of input formats, including:

• A 4-digit year value, from 1901 to 2155.
• The string form of the 4-digit year value, from '1901' to '2155'.
• A 2-digit year value, from 0 to 99. It is converted to a 4-digit year as follows:
  • Values in the range 1 to 69 will be converted to 2001 to 2069.
  • Values in the range 70 to 99 will be converted to 1970 to 1999.
  • 0 will be converted to 0000.
• The string form of a 2-digit year value, from '0' to '99'. It is converted to a 4-digit year as follows:
  • Values in the range '1' to '69' will be converted to 2001 to 2069.
  • Values in the range '70' to '99' will be converted to 1970 to 1999.

These are input-value conversion rules; they do not mean that a YEAR(2) column can be declared. MySQL 8 does not support the YEAR(2) data type. An input year with two digits is ambiguous, so use four-digit values when the intended century must be explicit.

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

MySQL YEAR syntax

This is the syntax of the YEAR data type:

column_name YEAR

Use YEAR without a display width in new definitions. MySQL 8 does not support YEAR(2); the explicit YEAR(4) display width is deprecated as of MySQL 8.0.19 and has the same meaning as YEAR. See the MySQL YEAR type documentation.

MySQL YEAR Examples

Let me see an example for MySQL YEAR.

First, let’s create the table named test_year with a column created_year which of YEAR type.

CREATE TABLE test_year(
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_year YEAR NOT NULL
);

Next, let’s insert a row with a 4-digit year value:

INSERT INTO test_year (created_year)
VALUES (2022);

Then, let’s insert 2 rows with 2-digit year values:

INSERT INTO test_year (created_year)
VALUES (10), (98);

Let’s insert one more 0 value :

INSERT INTO test_year (created_year)
VALUES (0), ('0');

Finally, let’s verify if the insertions is correct by querying the data from the table :

SELECT * FROM test_year;

+----+--------------+
| id | created_year |
+----+--------------+
|  1 |         2022 |
|  2 |         2010 |
|  3 |         1998 |
|  4 |         0000 |
|  5 |         2000 |
+----+--------------+

Here, we can see that the number 0 is converted to 0000, and the string '0' is converted to 2000.

Conclusion

In this tutorial, we introduced MySQL YEAR data types, and the rules for using YEAR data types.

• A 4-digit year value and a 2-digit year value are acceptable for YEAR columns.
• 2-digit years 1 to 69 will be converted 2001 to 2069.
• 2-digit years 70 to 99 will be converted 1970 to 1999.
• The numbers 0 will be converted to 0000.
• The string '0' will be converted to 2000.
• Use four-digit inputs when you need to avoid century inference for a two-digit year.