trunc(sysdate-to_date('2003-10-25','yyyy-mm-dd'))
select trunc(to_date(string1,'yyyy-mm-dd')-to_date(string2,'yyyy-mm-dd')) from dual;
我们知道在SQL SERVER中有datediff可以返回两个日期之间的时间间隔,但在Oracle中却不存在这样的函数,那要怎么实现呢?我们分两种情况讨论:
1.只是粗略返回相隔的天数
我们知道在Oracle中两个日期相减返回的就是相差的天数,当然这个天数未必是整数,它会把两个日期相差的时分秒折算成天数
SQL> SELECT SYSDATE-HIREDATE FROM EMP;
SYSDATE-HIREDATE
----------------
3.12123843
9316.66291
9314.66291
9275.66291
9096.66291
9246.66291
9207.66291
9046.66291
9116.66291
9030.66291
9030.66291
8979.66291
此时我们可以用TRUNC函数去获取我们所要得到的天数:
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE-HIREDATE) FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE-HIREDATE)
---------------- -----------------------
3.12486111 3
9316.66653 9316
9314.66653 9314
9275.66653 9275
9096.66653 9096
9246.66653 9246
9207.66653 9207
9046.66653 9046
9116.66653 9116
9030.66653 9030
9030.66653 9030
8979.66653 8979
当然也可以这样:
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE,'DDD')-TRUNC(HIREDATE,'DDD') FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE,'DDD')-TRUNC(HIREDATE,'DDD')
---------------- ------------------------------------------
3.12626157 3
9316.66793 9316
9314.66793 9314
9275.66793 9275
9096.66793 9096
9246.66793 9246
9207.66793 9207
9046.66793 9046
9116.66793 9116
9030.66793 9030
9030.66793 9030
8979.66793 8979
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
9275 16 31 +000009275 16:31:55.000000000
9096 16 31 +000009096 16:31:55.000000000
9246 16 31 +000009246 16:31:55.000000000
9207 16 31 +000009207 16:31:55.000000000
9046 16 31 +000009046 16:31:55.000000000
9116 16 31 +000009116 16:31:55.000000000
9030 16 31 +000009030 16:31:55.000000000
9030 16 31 +000009030 16:31:55.000000000
8979 16 31 +000008979 16:31:55.000000000