How To Find in Oracle DATE Difference

This online tutorial is based on examples to make it easier to follow. The Oracle Date type can be used to do subtraction, but the returning number will be given in days, as you see on the next example the returning number is integer:

SELECT (SYSDATE+1) - SYSDATE AS Days
  FROM DUAL;

When you are doing subtraction with the dates the Oracle sql will return a number in days. To turn the returned number in hours we need to multiply with 24 hours. The next Oracle date diff example we will do it – turning the output in hours.

SELECT ((SYSDATE+1) - SYSDATE) * 24 AS Hours
  FROM DUAL;

To change the result in minutes you will need to multiply the last returned number with 60 minutes. The following Oracle DBA oracle sql query will return the datediff oracle in minutes by multiply the result with 60 minutes.

SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 AS Minutes
  FROM DUAL;

The following Oracle DBA example with Oracle date diff will return the date result in seconds (multiply the result with 60 seconds).

SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS Seconds
  FROM DUAL;

To finding out the difference in months we will use the function MONTHS_BETWEEN that gives us the final result.

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-60))) AS Months
  FROM DUAL;

To find out date difference in years you would need to convert the number of days in an amount of month. Once you got a total number of month convert it to years by dividing it with 12 months. See the example below:

SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)) /12 ) AS Years
  FROM DUAL;

Now to finding out the difference in years, months and days together will make the query more complicated. See the query below:

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))/12) AS Years,
       MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))),12) AS Months,
       SYSDATE - ADD_MONTHS(
         (SYSDATE-600),TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)))
       ) AS Days
 FROM DUAL;

To use more date diff Oracle functions find out below the date format Oracle date masks and using oracle interval functions the date calculating will look more clear and easier to read.

See Also:
Oracle Date Format Home