MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

  • I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:

    UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123' 
    

    Unforutnately, I get the following error: Incorrect datetime value: '2013-08-25T17:00:00+00:00' for column 's_time' at row 1

    The datatype for s_time is DateTime.

    I have already attempted to set the allow_invalid_dates property using the workbench.

    Can anyone understand and please explain this error to me? I know that if I manually change the statement to UPDATE m_table SET s_time = '2013-08-25 17:00:00' WHERE id = '123', the statement works.

    Unfortunately, I cannot modify the program that supplies the SQL statement (which I'm told is valid by the creator of the program) and I also cannot understand what the +00:00 symbolises.

    Thanks

  • '2013-08-25T17:00:00+00:00'
    

    This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.

    The documentation explains what ALLOW_INVALID_DATES does:

    Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

    In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

    The +00:00 is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

    Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

    mysql> select @@sql_mode;
    +--------------------------------------------+
    | @@sql_mode                                 |
    +--------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
    +--------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
    ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1
    
    -- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
    -- current session -- it does not make a server-wide config change
    
    mysql> set @@sql_mode='no_engine_substitution';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@sql_mode;
    +------------------------+
    | @@sql_mode             |
    +------------------------+
    | NO_ENGINE_SUBSTITUTION |
    +------------------------+
    1 row in set (0.00 sec)
    
    -- now MySQL will accept the invalid value, with a warning
    
    mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +---------+------+-----------------------------------------+
    | Level   | Code | Message                                 |
    +---------+------+-----------------------------------------+
    | Warning | 1265 | Data truncated for column 'dt' at row 1 |
    +---------+------+-----------------------------------------+
    1 row in set (0.00 sec)
    
    -- the value did get inserted, but the time zone information was lost:
    
    mysql> select * from datetimetest;
    +----+---------------------+
    | id | dt                  |
    +----+---------------------+
    |  1 | 2013-08-26 12:00:00 |
    +----+---------------------+
    1 row in set (0.00 sec)
    

    Thank you. In the end I have asked the programmer to change the PHP code that created the SQL statement to meet the MySQL standard, but this is an interesting workaround. The strange thing is that the original SQL statement works in older MySQL versions.

    Including `STRICT_TRANS_TABLES` in a default configuration file was only introduced in MySQL 5.6, which explains the behavior change... if you enable this `SQL_MODE` in previous versions, the query would break in those versions, also.

    You just saved my life. Thanks a lot for the answer!

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM