MySQL issue updating DATETIME Field from ISO 8601

问题:

I have a table with a varchar field that contains ISO 8601 time format like 2015-01-18t10:00:10z.

I can use

select STR_TO_DATE('2015-01-18t10:00:10z','%Y-%m-%dt%H:%i:%s') 

and get the correct result:

2015-01-18 10:00:10

When I come to use this to insert into a DATETIME field I get:

update test set optin1 = STR_TO_DATE(optin,'%Y-%m-%dt%H:%i:%s')

Error Code: 1292 Truncated incorrect datetime value: '2015-01-18t10:00:10z'

Optin is the varchar ISO 8601 time column

Optin1 is the DATETIME field I want to update.

Anyone know why this is hapepenig and how to fix the issue?

Many Thanks.

回答1:

You should add the 'z' to your pattern,

update test set optin1 = STR_TO_DATE(optin,'%Y-%m-%dt%H:%i:%sz')

http://sqlfiddle.com/#!9/ea601/1