| Bug #78158 | Error 1292: "Incorrect datetime value" when inserting a datetime with trailing Z | ||
|---|---|---|---|
| Submitted: | 20 Aug 2015 22:37 | Modified: | 21 Aug 2015 22:34 |
| Reporter: | Pedro Acevedo | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.6.26 | OS: | Windows (64-bit server) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | datetime, trailing z, UTC, z | ||
[21 Aug 2015 10:26]
MySQL Verification Team
Thank you for the bug report. Please print here the output of insert into tester values ('2014-12-09T16:02:57.004Z'); on 5.1.61 with ISO-formatted datetime. Thanks.
[21 Aug 2015 13:32]
Pedro Acevedo
1 row(s) affected, 1 warning(s): 1264 Out of range value for column 'mycol' at row 1 It throws a warning, but it inserts the record. Nowhere in the docs does it say that the Z is invalid in a datetime literal.
[21 Aug 2015 22:34]
MySQL Verification Team
Thank you for the feedback. Please read the Manual about the sql_mode so you can't convert the warnings in error. The ISO datetime formant isn't supported for the MySQL.
C:\dbs>5.6\bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test
Database changed
mysql> create table tester(mycol datetime);
Query OK, 0 rows affected (0.27 sec)
mysql> insert into tester values ('2014-12-09T16:02:57.004Z');
ERROR 1292 (22007): Incorrect datetime value: '2014-12-09T16:02:57.004Z' for column 'mycol' at row 1
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tester values ('2014-12-09T16:02:57.004Z');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from tester;
+---------------------+
| mycol |
+---------------------+
| 2014-12-09 16:02:57 |
+---------------------+
1 row in set (0.02 sec)
Description: Inserting an ISO-formatted datetime with a trailing Z in a DATETIME column produces error 1292, "Incorrect datetime value for column at row 1". This worked in 5.1.61. How to repeat: create table tester(mycol datetime); /* This will cause an error. */ insert into tester values ('2014-12-09T16:02:57.004Z'); /* This works fine. */ insert into tester values ('2014-12-09T16:02:57.004');