SQL CHANGE DATE FORMAT IN BULK I have a database with several hundred fields but my data structure is wrong. It is currently in uk format as follows:

d/m/y 01/01/85 01/01/96 23/12/87

What would be the most efficient way to change the dates in bulk to sql standard of year/month/day

eg. 02/01/85 --> 1985/01/02
Plan A:  Clean up the dates as you load the data.  How are you loading it?  INSERT?  LOAD DATA INFILE?  It is possible to do the STR_TO_DATE() in either case.

Plan B:  (This requires "Plan A" for future inserts.)  Devise a script, possibly mostly inside a Stored Routine that would do the following for any given column known to be a VARCHAR that should be a DATE.  (Do something similar for a DATETIME.)  Here is a sketch of what is needed for one $column in one $table:


ALTER TABLE $table ADD COLUMN tmp DATE NOT NULL;
UPDATE $table SET tmp = STR_TO_DATE($column, '%d/%m/%Y');
ALTER TABLE $table DROP COLUMN $column, RENAME COLUMN tmp TO $column;