how do i change every date format in my table to another

[jasonc]

my field is called 'dob' and contains the following date format 'dd-mm-yyy'

 

is there a query that i can use to convert every date in this field from 'dd-mm-yyy' to 'yyy-mm-dd' format ?

 

i tried the following using a new field i created.

 

update `user_accounts` SET `DoB2`=DATE_FORMAT(`user_accounts`.`dob`, '%Y-%m-%d')

 

but this did not work, just return zero changed.

[Pikachu2000]

DATE_FORMAT() won't work the way you're trying to use it. It reformats a date that's already in YYYY-MM-DD format, but won't correct a date that's not in the proper format. You need the STR_TO_DATE() MySQL function.

 

I haven't tested this, but give it a shot. You'll need to adjust the format specifiers if the date is currently stored in anything other than [2 digit month]dash[2 digit day]dash[4 digit year].

 

UPDATE `user_accounts` SET `DoB2` = STR_TO_DATE(`dob`, '%m-%d-%Y');