Bug #82640 cast string to datetime produces invalid value
Submitted: 19 Aug 2016 3:30 Modified: 19 Aug 2016 7:54
Reporter: 帅 Bang Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6 OS:Linux
Assigned to: 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