CODEWITHSUNDEEP
mysqldatetime
nicholas.reichel
nicholas.reichel

Reputation: 2270

Truncated incorrect datetime value mysql

I am trying to convert my strings to datetimes, and it works for some but not all. I assume it's because mysql doesn't understand miliseconds. But is there a way to tell it to ignore the miliseconds, or how could I remove the part after the last period, make a new column, and convert that column to datetime. The result and input is shown below.

I am using set @@sql_mode='no_engine_substitution';, which I found on another stackoverflow question, but it doesn't seem to help. 

1292 Truncated incorrect datetime value: '2015-03-16 08.36.20.814000' 
Rows matched: 156192044  Changed: 105813258  Warnings: 156088004

I'm using the method suggested on my previous question to convert them. Convert String to Datetime Object in SQL

My update query is:

update marketwatch_data set `datetime` = STR_TO_DATE(`Update Time`,'%Y-%m-%d %h.%i.%s');

Upvotes: 3

Views: 11241

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use substring_index():

update marketwatch_data
    set `datetime` = STR_TO_DATE(substring_index(`Update Time`, '.', 3),
                                 '%Y-%m-%d %h.%i.%s');

Upvotes: 3

Related Questions