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.

Answer 1

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

Answer 2

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)