We know that in SQL Server, datediff can return the time interval between two dates, but such a function does not exist in Oracle. How can this problem be achieved? We will discuss the problem in two ways:
1. Just roughly return the number of days
We know that in Oracle, two dates subtract from each other and return a different number of days. Of course, this number of days is not necessarily an integer. It converts the time, minute, and second of the two dates into days.
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
In this case, we can use the trunc function to obtain the number of days we want:
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
Of course, you can also do this:
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. Return the exact interval
For example, if I want to get two dates separated by several days, several hours and several seconds, then trunc cannot be used. We can use numtodsinterval and extract to achieve this:
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