I am importing a CSV file I download from a website to display to clients in my PHP App. The date the website spits out in the CSV is not MySQL friendly. I want to convert it in the table immediately after importing the CSV file. The date in the CSV file is this format: February 06 2023 08:26:44
I’m running the following query:
update tblname set `time` = STR_TO_DATE(`time`, '%Y-%m-%d %H:%i:%s') where `time` = STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s');
This is the error I get in phpmyadmin:
MySQL said: Documentation
#1292 – Truncated incorrect datetime value: ‘February 06 2023 08:26:44’
Due to the nature of the log, I need the time to be kept as well. Any help much appreciated!
>Solution :
STR_TO_DATE(time, '%M %d %Y %H:%i:%s') parses the formatted date and returns the date that you want to store in the table:
UPDATE tblname
SET `time` = STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s');
The WHERE clause is not needed.