I just need help on how to convert date from MM/dd/yyyy to yyyy-MM-dd in MySQL?
I already asked this question a few months ago, and I was able to successfully convert it. Please see thread here.
However, I'm having difficulty with this new dataset. Previously, when I created thie previous post, ALL of my dataset were in the format MM/dd/yyyy, to which I successfully converted to yyyy-MM-dd using the following query:
Update human_resources.timekeeping Set Actual_Date = str_to_date(Actual_Date,'%m/%d/%Y');
However, for this new problem that I am encountering, I have a mix of data format in one table. Some time entries are in the format of MM/dd/yyyy while some are in the format yyyy-MM-dd. (Datatype is VARCHAR)
I need to change my old format MM/dd/yyyy to my new format yyyy-MM-dd.
I'm trying to use the same query:
Update human_resources.time_entry_request Set Actual_Date = str_to_date(Actual_Date,'%m/%d/%Y');
but I'm encountering the following error:
Error Code: 1411. Incorrect datetime value: '2021-05-08' for function str_to_date
On the other hand, I tried to use this query: (Specified the actual date in the where clause)
Update human_resources.time_entry_request Set Actual_Date = str_to_date(Actual_Date,'%m/%d/%Y') where Actual_Date = "05/31/2021";
and it was successful. (Changing specific MM/dd/yyyy time entry to format yyyy-MM-dd).
I believe the error that is causing this problem is that the dataset already contains the new and accurate format which is the yyyy-MM-dd.
It also happens vice versa, tried to change the new format into the old one so I can change them to the new one altogether.
PS. The data type for Actual_Date is VARCHAR. I tried changing it to DATE() but am receiving this error:
Executing:
ALTER TABLE `human_resources`.`time_entry_request`
CHANGE COLUMN `Actual_Date` `Actual_Date` DATE NULL DEFAULT NULL ;
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1292: Incorrect date value: '06/04/2021' for column 'Actual_Date' at row 17
SQL Statement:
ALTER TABLE `human_resources`.`time_entry_request`
CHANGE COLUMN `Actual_Date` `Actual_Date` DATE NULL DEFAULT NULL
Is there a way I can change the old format to the new one, please? Would really appreciate your help! The dataset contains a mix of MM/dd/yyyy while some are in the format yyyy-MM-dd
My previous post contains ALL MM/dd/yyyy so I was able to convert it successfully.