Convert text string in wrong format to date with in MySQL

I have this sql table in which I have a column with dates, but they are text strings and they are not in YYYY-MM-DD format. My dates are in DD/MM/YYYY.

How can I parse or convert those text strings into dates DD/MM/YYYY –> YYYY-MM-DD ?, Preferably in a new column, since I will be updating that table with new CSVs with the wrong date format.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You mean like

UPDATE t 
SET my_truly_is_date_col = STR_TO_DATE(my_wonky_stringdate_col, '%d/%m/%Y')
WHERE my_wonky_stringdate_col LIKE '%/%/%'

i.e. you’ll make a new column that really is a date, then fix all the strings into it, then remove the string col, then fix the program that loads the junk in so it uses the date column and everything is beautiful going forward? Yep.. That’d be good 😉

Method 2

SELECT STR_TO_DATE(‘[ 21/Apr/2009:07:14:50 +0100]’, ‘[ %d/%b/%Y:%H:%i:%S +0100]’);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0