[SOLVED] Complex date column case statement

Complex date column case statement

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.

Solution

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