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
Improve this question
1
  • And your question is what exactly`? Commented Mar 9, 2021 at 16:22

2 Answers 2

Reset to default
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)
Improve this answer
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 Commented Mar 10, 2021 at 15:14
  • please create a dbfiddle with some rows that produces the error. Commented Mar 10, 2021 at 16:22
  • @Carlo - What version of MySQL/MariaDB? I suspect (without proof) that handling the 'T' and '+02:00' is a recent addition. Commented Mar 11, 2021 at 18:16
  • @RickJames i'm using version 10.4.14-MariaDB Commented Mar 16, 2021 at 7:21
  • this version can handle the convertion, like i said add some sample dat to show us the error Commented Mar 16, 2021 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
Improve this answer
1
  • Still getting the same error on my side, so probably is caused by mine MySQL version Commented Mar 10, 2021 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.