|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:

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;

| improve this answer | |
  • can you please share the queries for conversion.Thanks – Divesh choudhary Jul 4 '16 at 5:50
  • added some suggested SQL – Rick James Jul 4 '16 at 17:26

I do understand You are trying to convert to some date/time data type. All You need then is CASE expression in Your query.

This should work then - assuming that those 3 variants are the only ones occurring, and that there are no additional leading or trailing whitespace characters.

Improving this case expression to include other variants should be rather easy though:

CASE date
    WHEN substring(date,3,1) = '-' THEN STR_TO_DATE (date,'%d-%m-%Y')
    WHEN substring(date,3,1) = '/' THEN STR_TO_DATE (date,'%d/%m/%Y')
ELSE STR_TO_DATE (date,'%Y/%m/%d') END
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.