1

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.

Michael Owen
  • 365
  • 3
  • 20

1 Answers1

2

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.

slaakso
  • 8,331
  • 2
  • 16
  • 27
  • Thanks slaakso, that did the trick, is there anyway to do it on the same column itself, or do I have to do it on a new column? Lastly is there a link or something just to understand '%d-%b-%y' rather than a copy/paste job from my end as want to learn as to what is going on in the code. – Michael Owen Feb 06 '20 at 13:04
  • You better add a `date` column, use `update`-statement to convert the strings to date and then drop the `varchar`-column. See the links to the manual pages. – slaakso Feb 06 '20 at 13:10
  • Just out of curiousity, how would you deal with a value that had no "day" element so for example "January 2019", i am guessing you would have to completely change the case statement as there wouldn't be a divider value "/" or "-" as well right? Btw, my question has been answered, this is just to understand more elements of date values. – Michael Owen Feb 06 '20 at 15:19
  • @MichaelOwen A date has day, month and the year part. If your data has only the month and the year, you would need to decide what day that string represents (first / last / some other day) in that month. If your data has more arbitrary notations for date in `varchar`, you might want to consider making a function that parses the data and produces the date. With a function you would have more flexibility on parsing the data than with simple `CASE`-statement. – slaakso Feb 06 '20 at 15:32