Search code examples
mysqldatetimesql-updatedate-formatting

Convert date format in MySQL column with Update query

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.