How to convert date from MM/dd/yyyy to yyyy-MM-dd in MySQL? Error Code: 1411. Incorrect datetime value: '' for function str_to_date - Asp.net

George
January 25, 2022
155 views
0 votes
2 Answers

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!

EDIT: The data type for Actual_Date is VARCHAR.

Apologies if my explanation may be a bit confusing. But I am using this data set to display and filter time entries in a gridview. When I am filtering dates, say for example (01/15/2022-01/25/2022), data from 2021 is also being displayed. When I tried to manually change the format of some of my data in sql to yyyy-MM-dd, my code seemed to be working fine. The problem is there are a lot of data in this table, which is why manually updating the format is impossible. What is the first thing that I need to do? I’m sorry this is all still a bit confusing for me.

Answers

- PSalmon
- January 25, 2022 at 9:12 am
- 0 votes
0
Your separator is / not –

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

Login or Signup to reply.

- user1191247
- January 25, 2022 at 9:42 am
- 0 votes
0
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');
```
Login or Signup to reply.