How to update date format in all records in mysql-CodePudding

I have a field "date_of_service" with data type "Varchar". Currently we insert record with mm/DD/yyyy format. Now I want to just update format to yyyy-mm-dd. How I can update them with query?

CodePudding user response：

Update via a formatting a parsed date:

update mytable set
date_of_service = date_format(str_to_date(date_of_service, '%m/%d/%Y'), '%Y-%m-%d`)

Or, use regex!

update mytable set
date_of_service = regexp_replace(date_of_service, '(.*?)/(.*)/(.*)', '\\3-\\1-\\2')

Please

CodePudding user response：

You should create a new field with DATE type in your table, maybe called new_date.

You have to separate your old dates using SUBSTRING function, first position of a string is 1, so year position start at 7 and take 4 chars, for example. Code will be something like this:

UPDATE your_table SET new_date = CONCAT(SUBSTRING(old_date,7,4),'-',SUBSTRING(old_date,1,2),'-',SUBSTRING(old_date,4,2));

CodePudding user response：

Ideally you should have just made the date_of_service field a date column. That being said, if you want to keep it as text but change formats, then use:

UPDATE yourTable
SET date_of_service = DATE_FORMAT(STR_TO_DATE(date_of_service, '%m/%d/%Y'),
                                  '%Y-%m-%d');

Another approach, using substring operations:

UPDATE yourTable
SET date_of_service = CONCAT(RIGHT(date_of_service, 4), '-',
                             LEFT(date_of_service, 2), '-',
                             SUBSTRING(date_of_service, 4, 2));