Search code examples
mysqldatemysql-workbenchmysql-error-1292

MySQL 1292 Truncated incorrect datetime value: '2020-09-11T08:32-50Z'


I need your help. I am trying to transform a text field (containing: 2020-09-11T08:32:50Z) into a date field. I have tried with TIMESTAMP statement and I get the warning: 1292 Truncated incorrect datetime value. I have also tried with STR_TO_DATE statement and I get another warning: 1411 Incorrect datetime value. I have entered SET @@SESSION.sql_mode='ALLOW_INVALID_DATES' to avoid some configuration error, but the error remains.

Thanks a lot!


Solution

  • MySQL understands the format up to the 'Z'. If you remove the 'Z' it will work fine.

    Demo:

    mysql> select date('2020-09-11T08:32:50Z');
    +------------------------------+
    | date('2020-09-11T08:32:50Z') |
    +------------------------------+
    | 2020-09-11                   |
    +------------------------------+
    1 row in set, 1 warning (0.01 sec)
    
    mysql> show warnings;
    +---------+------+------------------------------------------------------------+
    | Level   | Code | Message                                                    |
    +---------+------+------------------------------------------------------------+
    | Warning | 1292 | Truncated incorrect datetime value: '2020-09-11T08:32:50Z' |
    +---------+------+------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date('2020-09-11T08:32:50');
    +-----------------------------+
    | date('2020-09-11T08:32:50') |
    +-----------------------------+
    | 2020-09-11                  |
    +-----------------------------+
    1 row in set (0.00 sec)
    

    No warning is returned if the 'Z' is removed.