1.只是粗略返回相隔的天数
我们知道在Oracle中两个日期相减返回的就是相差的天数，当然这个天数未必是整数，它会把两个日期相差的时分秒折算成天数
SQL> SELECT SYSDATE-HIREDATE FROM EMP;
SYSDATE-HIREDATE
----------------
3.12123843
9316.66291
9314.66291
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE-HIREDATE) FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE-HIREDATE)
---------------- -----------------------
3.12486111 3
9316.66653 9316
9314.66653 9314
2.返回精确的时间间隔
比如我要得到两个日期相隔几天几小时几分几秒，这时就不能用TRUNC来实现了，我们可以用NUMTODSINTERVAL和EXTRACT来实现：
SQL> SELECT EXTRACT(DAY FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) DAY,
2 EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) HOUR,
3 EXTRACT(MINUTE FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) MINUTE,
4 NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY') DETAIL
5 FROM EMP;
DAY HOUR MINUTE DETAIL
---------- ---------- ---------- ------------------------------
3 3 31 +000000003 03:31:54.999999999
9316 16 31 +000009316 16:31:55.000000000
9314 16 31 +000009314 16:31:55.000000000
