MySQL – Truncated incorrect datetime value

I need to migrate some data from between to tables. The first has a varchar(255) field where a date is stored. I want to insert that date in a timestamp field in the second table.

The varchar field store the value in this ISO 8601 format: 2020-09-24T15:08:07+02:00.

I tried different conversions using STR_TO_DATE and casts but I always get the same error:

Truncated incorrect datetime value: ‘2020-06-01T09:38:08+02:00’

On selecting the value seems to be converted correctly:

SELECT STR_TO_DATE(varcharDate,'%Y-%m-%dT%H:%i:%s') = 2020-06-01 09:38:08

In would recommend

  SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)

STR_TO_DATE does the trick, but is usually slow.

You can run such a Query to test the performance using the column name instead of the text

 CREATE TABLE t2 (tz TIMESTAMP) SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)

You should create table with the timestamp datatype & then try to insert :

create table Timestamp_checking
(col_date timestamp(6) ) ;

0 row(s) affected

insert into Timestamp_checking values ('2020-06-01T09:38:08+02:00') 

1 row(s) affected   0.022 sec