Why is it not good practice to use date values with two-digits years in MySQL?

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

MySQL interprets 2-digit year values with the help of following rules −

Year values in the range 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 the year 2000.

Daniol Thomas

Published on 19-Feb-2018 10:54:13

Previous Page Print Page