We help IT Professionals succeed at work.

Case statement using dates

Beverly Penney
on
I have the following case statement in a query however it is bringing back the incorrect values,  everything is showing up in the result set as 'Greater than 61 days' however there is a range of dates that should be falling into the various categories.


  case (when  trunc(creation_date)  between trunc(sysdate) and trunc(sysdate - 7) then '7 Days'
              when  trunc(creation_date) between trunc(sysdate -8) and trunc(sysdate -31) then '8-30 Days'
              when  trunc(creation_date)  between trunc(sysdate -32) and trunc(sysdate -61) then '30 - 60 Days'
              else 'Greater 61 Days') end as Aging
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
When using between the lower value must be on the left side:
case (when  trunc(creation_date)  between trunc(sysdate-7) and trunc(sysdate) then '7 Days'
   

Same for the rest of them

Explore More ContentExplore courses, solutions, and other research materials related to this topic.