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

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!

CodePudding user response：

Try the following query using STR_TO_DATE:

select STR_TO_DATE(col1,'%Y-%m-%dT%TZ') as my_date  
from test;

Result:

my_date
2020-09-11 08:32:50

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=90ac6bb62834aebc4732afb2476227c1

%Y --- > Year as a numeric, 4-digit value

%m --- > Month name as a numeric value (01 to 12)

%d --- > Day of the month as a numeric value (01 to 31)

%T --- > Time in 24 hour format (hh:mm:ss)

CodePudding user response：

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.