SQL CHANGE DATE FORMAT IN BULK

Question

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

Answer 1

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:

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