2

I need to cleanse a column in a table having different date formats.

eg :

%d-%b-%y, %Y-%B-%d, %d/%m/%Y, %d%b%y, %Y-%b-%d, %Y-%m-%d, %d %b %y, %m/%d/%Y

and so on

Now i need to convert all date values to uniform format like **

%Y-%m-%d

**.

Saharsh Shah
  • 27,121
  • 8
  • 41
  • 77
MaDHaN MinHo
  • 519
  • 5
  • 22

1 Answers1

2

You have to update table data. You can use STR_TO_DATE() function to convert into date.

for e.g., 

UPDATE table1 = STR_TO_DATE(column1, '%d-%b-%y') WHERE STR_TO_DATE(column1, '%d-%b-%y') IS NOT NULL;
UPDATE table1 = STR_TO_DATE(column1, '%Y-%B-%d') WHERE STR_TO_DATE(column1, '%Y-%B-%d') IS NOT NULL;
UPDATE table1 = STR_TO_DATE(column1, '%d/%m/%Y') WHERE STR_TO_DATE(column1, '%d/%m/%Y') IS NOT NULL;
UPDATE table1 = STR_TO_DATE(column1, '%d%b%y') WHERE STR_TO_DATE(column1, '%d%b%y') IS NOT NULL;

etc......

Saharsh Shah
  • 27,121
  • 8
  • 41
  • 77
  • how to differentiate month and date with cases like this? select DATE_FORMAT(STR_TO_DATE('2015-08-09', '%Y-%m-%d'),'%Y-%b-%d') , DATE_FORMAT(STR_TO_DATE('2015-08-09', '%Y-%d-%m'),'%Y-%b-%d') ; it returns different results – MaDHaN MinHo Dec 18 '15 at 10:25