Bug #82640 cast string to datetime produces invalid value

• Submitted: 19 Aug 2016 3:30
• Modified: 19 Aug 2016 7:54
• Reporter: 帅 Bang
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Linux
• CPU Architecture: Any

[19 Aug 2016 3:30] 帅 Bang

Description:
mysql> drop table if exists sb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table sb(a datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sb values("9-01-01 12:34:56");
Query OK, 1 row affected (0.01 sec)

mysql> select * from sb;
+---------------------+
| a                   |
+---------------------+
| 0009-01-01 12:34:56 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.22    |
+-----------+
1 row in set (0.00 sec)

According to the doc(http://dev.mysql.com/doc/refman/5.6/en/datetime.html) , the supported range of datetime is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

How to repeat:
drop table if exists sb;
create table sb(a datetime);
insert into sb values("9-01-01 12:34:56");
select * from sb;

Suggested fix:
 insert "9-01-01 12:34:56" into column of datetime type produces 2009-01-01 12:34:56 rather than  0009-01-01 12:34:56

[19 Aug 2016 4:12] 帅 Bang

Suggested fix:
 insert "9-01-01 12:34:56" into column of datetime type produces 2009-01-01 12:34:56 or just failed

[19 Aug 2016 7:54] MySQL Verification Team

Hi Bang,

Thank you for the report.
I would say this is duplicate of Bug #82642(with strict mode - invalid date inserted when single digit year used). 

Thanks,
Umesh