0

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

  • Are you storing these as (var)char data types? – eroomydna Apr 4 '15 at 18:03
  • Doesn't he have to be? I mean, normally it's the client which imposes the diplay format. – Vérace Apr 4 '15 at 19:05
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.