How to update date format in all records in mysql

Solution 1:

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

Solution 2:

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