Convert text string in wrong format to date with in MySQL-CodePudding

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.

thanks!!

CodePudding user response：

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 ;)

CodePudding user response：

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