ORACLE SQL CAST IN CASE STATEMENT

Question

Is there a way to CAST as an INTEGER within a CASE Statement ?

CASE 
WHEN SV.END_DATE IS NOT NULL THEN SV.END_DATE - SV.START_DATE 
ELSE 0
END AS DAYDIFF

What are you trying to do - round, or truncate, the difference between end_date and start_date? Get the number of whole days between them? or something else? Sample data and expected results might make things clearer too. — Alex Poole
– Alex Poole, Commented May 17, 2018 at 15:47

Alex Poole · Accepted Answer · 2018-05-17 15:56:30Z

5

Yes, you could cast the value to a numeric data type with no fractional part:

CASE 
  WHEN SV.END_DATE IS NOT NULL THEN
    CAST(SV.END_DATE - SV.START_DATE AS NUMBER(38))
  ELSE 0
END AS DAYDIFF

or you could truncate the result, which also strips the fractional part of the value:

CASE 
  WHEN SV.END_DATE IS NOT NULL THEN
    TRUNC(SV.END_DATE - SV.START_DATE)
  ELSE 0
END AS DAYDIFF

answered May 17, 2018 at 15:52

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much, appreciate your support.