I had a large dataset with all the date fields in the format DD/MM/YYYY. I needed to convert them to YYYY-MM-DD . I ran the following queries:
UPDATE TABLE_NAME SET FIELD_NAME = replace(FIELD_NAME, '/', '-');
UPDATE TABLE_NAME SET FIELD_NAME = str_to_date(FIELD_NAME,'%d-%m-%Y');
Hope it helps…
Hey thank you very much for this post; It was exactly what I needed.
I found that your query did not work for me, but it gave me an idea that eventually did work to change a string of month/day/year (e.g. 12/31/2010) into a date formatted YYYY-MM-DD (e.g. – 2010-12-31)
update TABLE_NAME set date_format(str_to_date(FIELD_TO_BE_CHANGED, ‘%m/%d/%Y’), ‘%Y-%m-%d’);
it is not working for me….mysql 5.7.21 i am using
UPDATE
TABLE_NAME
SET
FIELD_NAME = concat(SUBSTRING(FIELD_NAME,7,4),’-‘,SUBSTRING(FIELD_NAME,4,2),’-‘,SUBSTRING(FIELD_NAME,1,2))
Leave a Reply