CODEWITHSUNDEEP
sqloracle-databasecaseto-dateto-char
Blowers
Blowers

Reputation: 75

Convert String to Date in CASE expression Oracle SQL

I'm using the following CASE expression, but I want the field to be a Date and not string format:

CASE WHEN TO_CHAR(DATE, 'MM/DD/YYYY') = '01/01/1800' THEN '01/01/2029'  
         ELSE TO_CHAR(DATE, 'MM/DD/YYYY')
     END as ENDDATE,

I tried adding in TO_DATE( before the TO_CHAR, but I keep getting an error on the Then part. Any pointers to how to successfully add in TO_DATE to this statement?

Upvotes: 0

Views: 1271

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

You could use date literal (which is always yyyy-mm-dd) and compare it directly to your date_column:

case when trunc(date_column) = date '1800-01-01' then date '2029-01-01'
     else date_column
end as enddate

Upvotes: 2

Krishna
Krishna

Reputation: 481

select 
case when date_column=to_date('01/01/1800','dd/mm/yyyy') then to_date('01/01/2029','dd/mm/yyyy')
     else date_column end as enddate
from dual;

You just have to make sure all the possible outcomes of the case statement belong to the same data type.

Upvotes: 0

Related Questions