How to convert date from MM/dd/yyyy to yyyy-MM-dd in MySQL? Error Code: 1411. Incorrect datetime val-CodePudding

I have a large dataset with employees' time entries. The current date format is MM/dd/yyyy. However, I need to convert all the dates into yyyy-MM-dd format.

I have tried the following:

Update human_resources.timekeeping Set Actual_Date = str_to_date(Actual_Date,'%d-%m-%Y');

Got the errror messsage Error Code: 1411. Incorrect datetime value: '' for function str_to_date.

My SQL version is 5.7.18-log.

I tried to view SQL mode using SELECT @@sql_mode; and I got NO ENGINE SUBSTITUTION.

I have tried to retrieve the value like shown below and it was working fine. Converting varchar mm/dd/yy to date format yyyy-mm-dd

However, updating the data would not work. I need to update the actual records, not insert new records.

Hope someone can help me regarding this. Thank you in advance!

CodePudding user response：

Your separator is / not -

%d-%m-%Y >> %d/%m/%Y

CodePudding user response：

My apologies if you have already taken the following things into consideration but I thought them worth mentioning.

Given that you say this is a "large dataset" I assume this is a table that is currently in use. Does the existing application rely on the Actual_Date being in that string format? Does it rely on a fixed number of columns in the table? Some poorly written applications can be very brittle when it comes to changing underlying data structure.

You may want to consider creating a copy of the current table, modifying the structure of the copy, and replacing the original with a view with the same columns and formats as the original. This way you get improved data but reduce risk to existing application.

In the title and first line of your question you state that the current format is MM/dd/yyyy

Update human_resources.timekeeping Set Actual_Date = str_to_date(Actual_Date,'%m/%d/%Y');