Comparing the Oracle Interval Data Type

Question

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 1

Your question mentions the INTERVAL datatype, although it is not clear from your examples where or if that is happening. Arithmetic with dates and intervals is quite simple;

SQL> select d1
  2         , d2
  3         , case when d2  > d1 + to_dsinterval ('10 0:0:0')
  4                 then 'no'
  5                 else 'yes' end
  6  from t34
  7  /

D1        D2        CAS
--------- --------- ---
02-JUL-10 25-JUN-10 yes
02-JUL-10 24-AUG-10 no
02-JUL-10 26-MAY-10 yes
02-JUL-10 25-JUL-10 no
02-JUL-10 15-APR-09 yes
02-JUL-10 13-JUL-10 no

6 rows selected.

SQL>

When both columns are of INTERVAL datatype, arithmetic clearly works:

SQL> select intvl1
  2         , intvl2
  3         , case when intvl2  > intvl1
  4                 then 'no'
  5                 else 'yes' end
  6  from t34
  7  /

INTVL1               INTVL2               CAS
-------------------- -------------------- ---
+10 00:00:00.000000  +07 00:00:00.000000  yes
+10 00:00:00.000000  -53 00:00:00.000000  yes
+10 00:00:00.000000  +37 00:00:00.000000  no
+10 00:00:00.000000  -23 00:00:00.000000  yes
+10 00:00:00.000000  +78 00:00:00.000000  no
+10 00:00:00.000000  -11 00:00:00.000000  yes

6 rows selected.

SQL>

Comparing the difference between two dates and an interval is a cinch...

SQL> select d1
  2         , d2
  3         , intvl1
  4         , case when  numtodsinterval(d1-d2, 'DAY') > intvl1
  5                 then 'no'
  6                 else 'yes' end
  7  from t34
  8  /

D1        D2        INTVL1               CAS
--------- --------- -------------------- ---
02-JUL-10 25-JUN-10 +10 00:00:00.000000  yes
02-JUL-10 24-AUG-10 +10 00:00:00.000000  yes
02-JUL-10 26-MAY-10 +10 00:00:00.000000  no
02-JUL-10 25-JUL-10 +10 00:00:00.000000  yes
02-JUL-10 15-APR-10 +10 00:00:00.000000  no
02-JUL-10 13-JUL-10 +10 00:00:00.000000  yes

6 rows selected.

SQL>

In short, it is difficult to understand what you are doing to get an error. So you need to edit your question and provide more details. Describe the table. Provide some sample data. Also, give us the entire error message.

Answer 2

If I execute the following on my 10g database, all works well:

SQL> create table temptbl (d1 date, d2 date);

Table created

SQL> insert into temptbl values (sysdate, sysdate-1/12);

1 row inserted

SQL> insert into temptbl values (sysdate, sysdate+1/12);

1 row inserted

SQL> SELECT CASE WHEN d1 - d2 > 0 THEN 'No' ELSE 'YES' END result FROM temptbl;

RESULT
------
No
YES

What else are you doing in this statement?

Answer 3

If you're performing arithmentic on INTERVAL DAY TO SECOND types, you need to compare it to another INTERVAL DAY TO SECOND column.

SQL> SELECT
  2         CASE when (NUMTODSINTERVAL(1, 'DAY') - NUMTODSINTERVAL (1, 'HOUR')) 
                        > (numtodsinterval(0, 'day'))
  3              then 1
  4              else 0
  5         end as expression
  6*  from dual
med_audit@AGDEV:SQL> /

EXPRESSION
----------
         1

Answer 4

At a guess, I'd say that your columns are TIMESTAMPs. When you subtract two DATEs, the result is a NUMBER; when you subtract two TIMESTAMPs, the result is an INTERVAL. To fix your second query, replace SYSDATE with SYSTIMESTAMP:

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

However, you'd be better off using TO_DSINTERVAL, as has already been suggested.

Answer 5

I discovered when I added "day to second" it turned the second value into an interval and it worked. Thanks all for the ideas. Once I get a couple more points (15 points is minimum for upvoting) I'll upvote everyone.

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