How To Find in Oracle DATE Difference

How To Find in Oracle DATE Difference

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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;

dba oracle dba management database management system oracle performance tuning oracle datediff oracle datediff in oracle date oracle date in oracle online courses online

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*