CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Samaritan_Learner
Samaritan_Learner

Reputation: 557

how to do CASE in date in oracle sql

How to check the null and return as date in case of null.

 CASE  WHEN SRC.End_Date is NOT NULL THEN 
 CAST((FROM_TZ(CAST(SRC.End_Date  AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Belfast') AS DATE AS END_DATE, //I am getting Syntax error in AS before end_date
 ELSE 
 CAST((FROM_TZ(CAST(destination.enddate AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Belfast') AS DATE AS END_DATE,

Upvotes: 1

Views: 60

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

CAST((FROM_TZ(CAST(NVL(SRC.End_Date, destination.enddate) AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Belfast') AS DATE) AS END_DATE

would be shorter

Upvotes: 2

Radagast81
Radagast81

Reputation: 3006

I think you're just missing some paranthesis:

... AS DATE)

And then put the column alias behind the END-keyword of the case statement:

...
END AS END_DATE

All together:

CASE  WHEN SRC.End_Date is NOT NULL 
      THEN CAST((FROM_TZ(CAST(SRC.End_Date  AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Belfast') AS DATE)
      ELSE CAST((FROM_TZ(CAST(destination.enddate AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Belfast') AS DATE)
END AS END_DATE,

Upvotes: 2

Related Questions