Bug #67984 Don't allow insertion of not supported dates

• Submitted: 28 Dec 2012 17:51
• Reporter: Sveta Smirnova
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S4 (Feature request)
• Version: 5.5.30, all
• OS: Any
• CPU Architecture: Any

[28 Dec 2012 17:51] Sveta Smirnova

Description:
According to http://dev.mysql.com/doc/refman/5.5/en/date-and-time-type-overview.html MySQL supports only dates in range of  '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. However one can successfully insert such dates even if ALLWO_INVALID_DATES SQL mode is not in use.

How to repeat:
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `foo` (
    -> `update_date` datetime NOT NULL
    -> ) engine=innodb;
Query OK, 0 rows affected (0.28 sec)

mysql> insert into foo () values('0024-06-21 10:35:55');
Query OK, 1 row affected (0.09 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from foo;
+---------------------+
| update_date         |
+---------------------+
| 0024-06-21 10:35:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='strict_all_tables,strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo () values('0024-06-21 10:35:55');
Query OK, 1 row affected (0.12 sec)

mysql> show warnings;
Empty set (0.00 sec)

Suggested fix:
Don't allow such dates.

See http://www.mysqlperformanceblog.com/2012/09/04/odd_date_values_and_input_range_checking/ for example of difficulties this "feature" can create.