Convert date format in MySQL column with Update query

Question

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!

You tell that "The date in the CSV file" - but use UPDATE which means that this date is already imported (incorrectly imported!) into the table. So we'd know all details of this skipped step. — Akina
– Akina, Commented Feb 8, 2023 at 20:05
You can't change the format of a DATETIME column. You do formatting when retrieving dates, not when storing them. — Barmar
– Barmar, Commented Feb 8, 2023 at 20:06
set time = STR_TO_DATE(time, '%Y-%m-%d %H:%i:%s') - This doesn't make sense to me. You're trying to update the time column to a formatted/parsed version of... itself? Was this "February 06 2023 08:26:44" value already imported into the DB? What is the type of the time column? What value does it contain when executing this query? — David
– David, Commented Feb 8, 2023 at 20:06
If the column is DATETIME datatype, you have to call STR_TO_DATE() when importing from the CSV. — Barmar
– Barmar, Commented Feb 8, 2023 at 20:07
Hi David. The CSV file has the date as 'February 06 2023 08:26:4'. The column currently has a VARCHAR value. It's a huge log that I'm importing and need to query later based on event time. — Ronaldo
– Ronaldo, Commented Feb 8, 2023 at 20:08

forpas · Accepted Answer · 2023-02-08 20:08:55Z

3

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.

answered Feb 8, 2023 at 20:08

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert date format in MySQL column with Update query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related