mysqldatestr-to-date

How to convert mixed dates in MYSQL to yyyy-mm-dd format

I have a text type column named start_date that contains many dates in 2 date different formats. One is dd/mm/yyyy and the other is yyyy-mm-dd.

I need to convert all dd/mm/yyyy dates to yyyy-mm-dd and then set the column data type to date.

UPDATE table_name SET start_date= str_to_date(start_date, '%d/%m/%Y');

So far I'm receiving "Error Code 1411. Incorrect datetime value: '2014-03-01' for function str_to_date"

Any help is much appreciated. Thanks

Solution

  • One is dd/mm/yyyy and the other is yyyy-mm-dd.

    If this is complete formats list then, for example, use

    UPDATE table_name 
SET start_date= STR_TO_DATE(start_date, '%d/%m/%Y')
WHERE LOCATE('/', start_date);