How to convert Varchar Date Value into Date Data Type in MySQL using Case When and Str_to Date

I have a list of dates in a table that currently holds that column as a Varchar data type. What I am looking to do is clean up this table and the first step is to convert this column into a Date Data Type and convert the values that I have to all be consistent.

The following is my create and insert statements, this is a sample of the Date Column:

Create Table:

CREATE TABLE “BU_TABLE_DATES" 
   (           "END_DATE" VARCHAR2(255 BYTE)
   );

Insert Statements:

INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('01/01/2018');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-18');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-19');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-19');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-20');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2018');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2019');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2020');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('17/07/2017');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('17/07/2019');

What I would like the final output to look like is the following (all Date datatype)

End_Date
01/01/2018
13/01/2018
13/01/2019
13/01/2019
13/01/2020
13/01/2018
13/01/2019
13/01/2020
13/01/2020
17/07/2017
17/07/2019

At present I have managed to write the following code using a case statement and str_to_date which helps to convert the values where the month is only a 2 character and also how I can have the format to show the year at the end even the code is written that way.

My Query

SELECT
    End_Date,
    CASE End_Date
        WHEN substring(End_Date,2,1) = '-' THEN STR_TO_DATE (End_Date,'%d-%m-%Y')
        WHEN substring(End_Date,2,1) = '/' THEN STR_TO_DATE (End_Date,'%d/%m/%Y')
    ELSE STR_TO_DATE (End_Date,'%d/%m/%Y') END as End_Date_New
FROM
    BU_TABLE_DATES
;

Output

End_Date    End_Date_New
01/01/2018  2018-01-01
13-Jan-18   (null)
13-Jan-19   (null)
13-Jan-19   (null)
13-Jan-20   (null)
13/01/2018  2018-01-13
13/01/2019  2019-01-13
13/01/2020  2020-01-13
13/01/2020  2020-01-13
17/07/2017  2017-07-17
17/07/2019  2019-07-17

Lastly in the code I am creating a new column, but I want to just change the datatype of the existing column but at present found a way to create a new column and then thought it might be best to then remove the old column, but ideally want this to work on the 1 column.

Would love some advice on how best to tackle this.

Thanks in advance.

Solution

You can use following to convert the strings to dates. You really should use DATE-datatype when you store dates. Using VARCHAR for dates will only get you into trouble.

SELECT
  End_Date,
  CASE substring(End_Date,3,1)
    WHEN '-' THEN STR_TO_DATE(End_Date,'%d-%b-%y')
    WHEN '/' THEN STR_TO_DATE(End_Date,'%d/%m/%Y')
  END as End_Date_New
FROM 
  BU_TABLE_DATES

See CASE statement and the STR_TO_DATE-function parameters (same as DATE_FORMAT's) from the manual.