Search code examples
mysqlsqldatedate-formattingsqldatatypes

Incorrect date format in MYSQL

I have been trying to convert varchar string in one of my column to DATETIME (to insert into another column of type DATETIME).

However I keep running into the following error: -

Error Code: 1411. Incorrect datetime value: '16MAY2017:09:30:00' for function str_to_date

My query to insert from one column into another looks like this: -

Update Data f1 set f1.Local_ETD_DT = str_to_date(f1.LOCAL_ETD, '%d/%M/%Y:%H:%i:%s');
Solution

  • Your data doesn't have slashes (/) between the day, month and year values. Remove them and you should be OK:

    Update Data f1 set f1.Local_ETD_DT = str_to_date(f1.LOCAL_ETD, '%d%M%Y:%H:%i:%s');
-- "/" Removed here ----------------------------------------------^-^