Change all varchar date formats with a single query |date(varchar) |
|--------------|
|2016-02-16    |
| -------------|
|03-12-2015    |
|--------------|
|07-07-2007    |
|--------------|
|2016-03-14    |


Dates are stored in different formats and type is varchar. How to change all formats with single query in MySQL?

The formats are d/m/y, d-m-y, and y/m/d.

I need to copy these to a new column in date format y-m-d
Create the new column as type DATE.

Then run 3 queries, one for each conversion needed.  Sure, they could be combined into a single query, but why bother; it just makes it more complex and likely to break.

And finally drop the old column and rename the new on into place?

The queries would be something like:

ALTER TABLE add COLUMN new_date DATE;
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d-%m-%Y') WHERE substring(date,3,1) = '-';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d/%m/%Y') WHERE substring(date,3,1) = '/';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%Y/%m/%d') WHERE substring(date,5,1) = '-';


Then do a SELECT to see if any rows still have a DEFAULT value for new_date;