0

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
1
  • And your question is what exactly`?
    – nbk
    Mar 9 at 16:22
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)
5
  • I'm always getting the same: Truncated incorrect datetime value: '2020-06-01T09:38:08+02:00' also tried to change the data type of the field to DATETIME but nothing has changed
    – Carlo
    Mar 10 at 15:14
  • please create a dbfiddle with some rows that produces the error.
    – nbk
    Mar 10 at 16:22
  • @Carlo - What version of MySQL/MariaDB? I suspect (without proof) that handling the 'T' and '+02:00' is a recent addition.
    – Rick James
    Mar 11 at 18:16
  • @RickJames i'm using version 10.4.14-MariaDB
    – Carlo
    Mar 16 at 7:21
  • this version can handle the convertion, like i said add some sample dat to show us the error
    – nbk
    Mar 16 at 8:48
0

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
1
  • Still getting the same error on my side, so probably is caused by mine MySQL version
    – Carlo
    Mar 10 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.