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!
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.