MySQL - Truncated incorrect datetime value

Question

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月24日T15: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

• nbk: And your question is what exactly`?

Answer 1

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)

• Carlo: I'm always getting the same: Truncated incorrect datetime value: '2020年06月01日T09:38:08+02:00' also tried to change the data type of the field to DATETIME but nothing has changed
• nbk: please create a dbfiddle with some rows that produces the error.
• Rick James: @Carlo - What version of MySQL/MariaDB? I suspect (without proof) that handling the 'T' and '+02:00' is a recent addition.
• Carlo: @RickJames i'm using version 10.4.14-MariaDB
• nbk: this version can handle the convertion, like i said add some sample dat to show us the error

Answer 2

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

• Carlo: Still getting the same error on my side, so probably is caused by mine MySQL version