Introduction to MySQL YEAR

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

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

The YEAR data type occupies 1 byte, and with a ​​range of 1901 to 2155, and 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.

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

In versions prior to MySQL 8.0.19, it was possible to use display-width properties such as: YEAR(2). However, the display-width property is now deprecated. And MySQL 8 is not supported YEAR(2) anymore. Please use YEAR directly.

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.