What is the difference between YEAR(2) and YEAR(4) in MySQL?

MySQLMySQLi Database

YEAR(2) stores a year in 2-digit format. For example, we can write 69 to store 1969 a year. In YEAR (2), the year can be specified from 1970 to 2069 (70 to 69).

YEAR(4) stores a year in 4-digit format. For example, we need to write 19669 to store 1969 as a year. In YEAR (4), the year can be specified from 1901 to 2155.

MySQL interprets 2-digit year values with the assistance of following rules:

  • Year values within the vary 00-69 are converted to 2000-2069.
  • Year values in the range 70-99 are converted to 1970-1999.

We must not store date values as a 2-digit format because values stored in this format becomes vague as the century is unknown.

It can be understood more clearly with the help of following MySQL example −

mysql> Create Table year_test(val year(2));
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> insert into year_test(val) values('70');
Query OK, 1 row affected (0.14 sec)

mysql> insert into year_test(val) values('00');
Query OK, 1 row affected (0.06 sec)
mysql> select * from year_test;
+------+
| val  |
+------+
| 70   |
| 00   |
+------+
2 rows in set (0.00 sec)

mysql> select * from year_test where val = '1970';
+------+
| val  |
+------+
| 70   |
+------+
1 row in set (0.03 sec)

mysql> select * from year_test where val = '2000';
+------+
| val  |
+------+
| 00   |
+------+
1 row in set (0.00 sec)

mysql> select * from year_test where val = '1900';
Empty set (0.06 sec)

It is uncertain that with which year we connote, ‘1900’ or ‘2000’, by storing 00 to ‘val’. MySQL is interpreting it as year 2000.

raja
Published on 19-Feb-2018 06:52:53
Advertisements