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

As we all know, YEAR(2) stores the year in 2-digit format. For example, we can write 69 to store 1969 as the 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 the following rules - Year values ​​in the range

• 00-69 are converted to 2000-2069.
• Year values ​​in the range 70-99 will be converted to 1970-1999.
ul>

We cannot store the date value in 2 digit format because the value stored in this format becomes Vague.

You can understand it more clearly with the help of the 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)

Copy after login

By storing 00 into "val", we are not sure which year we are referring to, "1900" or "2000". MySQL interprets this as the year 2000.

The above is the detailed content of Why is it not a good practice to use date values ​​with two-digit years in MySQL?. For more information, please follow other related articles on the PHP Chinese website!