jeffspost jeffspost - 9 months ago 53
SQL Question

Comparing the Oracle Interval Data Type

I have an oracle time interval in a select statement like this:

SELECT ...
CASE WHEN date1 - date2 > 0
THEN 'No'
ELSE 'YES'
END


This fails with an invalid data type. So I tried this, and it still fails.

SELECT ...
CASE WHEN date1 - date2 > (sysdate - sysdate)
THEN 'No'
ELSE 'YES'
END


Is there any way to compare interval types?

Answer Source

I discovered when I added day to second it turned the second value into an interval and it worked. Thanks all for the ideas.

SELECT ...
    CASE WHEN date1 - date2 > (sysdate - sysdate) day to second
    THEN 'No'
    ELSE 'YES'
END