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.
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.