Complex date column case statement

Question

I need a small help, I am having a date field that has a format like this in a column COL1

COL1

yyyy-MM-dd
dd-MMM-yy
ddMMMYYYY

The query I have is

    case WHEN ("COL1" like '%-%') THEN to_localdate('yyyy-MM-dd', "COL1")  
    WHEN ("COL1" like '%-%') THEN to_localdate('dd/MMM/yy', "COL1") 
    ELSE to_localdate('ddMMMyyyy', "COL1") END AS COL1

I am not sure how to process the dd-MMM-yy field here in my case statement.

Thanks,

Note: I am using Oracle and running queries on denodo. But this is just SQL case statement. So it should work everywhere.

But of course the entire difficulty comes from the serioiusly flawed data design of using a string data type for dates instead of oracle's DATE. With a proper data type for dates, this current problem (and many future problems) ceases to exist. — EdStevens, May 18 '21 at 16:00

score 3 · Accepted Answer · edited May 18 '21 at 15:25

3

Assuming that the column contains valid formatted dates, use the _ wildcard which represents a single character:

CASE 
  WHEN ("COL1" LIKE '____-__-__') THEN to_localdate('yyyy-MM-dd', "COL1")  
  WHEN ("COL1" like '__/___/__') THEN to_localdate('dd/MMM/yy', "COL1") 
  ELSE to_localdate('ddMMMyyyy', "COL1") 
END AS COL1

edited May 18 '21 at 15:25

Olivier Jacot-Descombes

104,806
13
138
188

answered May 18 '21 at 15:13

forpas

160,666
10
38
76

@Olivier Jacot-Descombes thanks for the correction. – forpas May 18 '21 at 15:26

score 0 · Answer 2 · edited May 18 '21 at 15:23

0

You can use regexp_like(). I think this is the logic:

(case when regexp_like('^[0-9]{4}-[0-9]{2}-[0-9]{2}$' 
     then to_localdate('yyyy-MM-dd', COL1) 
     when regexp_like('^[0-9]{2}-[a-zA-Z]{3}-[0-9]{2}$' 
     then to_localdate('dd-MMM-rr', COL1) 
     when regexp_like('^[0-9]{2}[a-zA-Z]{3}[0-9]{2}$' 
     then to_localdate('ddMMMrr', COL1) 
end)

edited May 18 '21 at 15:23

Olivier Jacot-Descombes

104,806
13
138
188

answered May 18 '21 at 15:11

Gordon Linoff

1,242,037
58
646
786

Complex date column case statement

2 Answers2