Updating a date format in MySQL

Question

I am working on a table that has two formats of dates stored in a field some are in mm/dd/yy and the newer entries are in yyyy/mm/dd like they should be.

I want to run an query like this

UPDATE table
   SET date_field = DATE_FORMAT(date_field, '%Y/%m/%d') 
WHERE date_field = DATE_FORMAT(date_field, '%m/%d/%y')

But it is just not working out. One result that I got was that it was just taking the %m data and turning it into the %Y and really messing up the data.

Any thoughts?

Answer 1

You want to use STR_TO_DATE function, not DATE_FORMAT. Plus, I assume you only want to update the misformed dates, so I guess you could do this :

UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) <> '0000-00-00';

P.S. Tables contain columns, not fields. And you shouldn't use a string type to hold your dates, but the DATE type