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

Updated on 20-Jun-2020 06:21:36

• Related Questions & Answers
• Is it a good practice to end switch with defaults in JavaScript?
• Is it a good practice to place all declarations at the top in JavaScript?
• Why is it not good to sleep on the stomach for the whole night?
• Why Rainy season is not considered good to start exercising?
• Why is it good to write the numbers in MySQL INTERVAL() function in ascending order?
• Why is it considered a bad practice to omit curly braces in C/C++?
• Why is it not recommended to use the mixture of quoted as well as unquoted values in MySQL IN() function’s list?
• Why is it necessary to declare NOT FOUND handler while using MySQL cursor?
• Why we cannot use MySQL DATE data type along with time value?
• Is it okay to store double and date in VARCHAR with MySQL?
• Is it possible to use UPDATE query with LIMIT in MySQL?
• Why it is better not to create numbers as objects in JavaScript?
• What is SciPy and why should we use it?
• Why is feedback necessary for good business?
• How to find last date from records with date values in MySQL?