CODEWITHSUNDEEP
mysql
momalone
momalone

Reputation: 22

Mysql wrong date format as text

I couldn't find answer of that question..

I have text column like " 18 August 2021 Wednesday" ; it is both wrong type and also wrong format (text);

How can I convert it to standard mysql date format and date type?

Thanks

I tried this:

UPDATE Table2
SET gun = DATE(STR_TO_DATE(gun, '%m/%d/%Y'));

Upvotes: 0

Views: 333

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You should first create a new date column to store the proper date result:

ALTER TABLE Table2 ADD COLUMN gun_date DATE;

Then, populate that date column with the output of STR_TO_DATE:

UPDATE Table2
SET gun_date = STR_TO_DATE(gun, '%d %M %Y');

If you'd like, you can drop the original gun column if you think you won't need it anymore.

Upvotes: 2

Related Questions