How To Find in Oracle Date Difference
  Home
 

This online tutorial page is based on examples to be easier to follow. Now about the Oracle Date type; there are many different ways to use the Oracle Date type for calculations and one of them is subtracting. The output below returns the value in number of days as it is in the following example:

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

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Doing subtraction with the dates the Oracle datediff will return a number in a day unit. To turn it in hours we need to multiply with 24 hours. As it is written in the next example.

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

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To change the result in minutes you will need to multiply the result above with 60 minutes. The following SQL query will return the date diff Oracle in minutes by multiply the result with 60 minutes.

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

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The fourth Online Tech Support 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;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

This example is how to find the differences in months Online Tech Support will use function MONTHS_BETWEEN that gives us the final result.

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

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The next date calculation is to find amount of years. You would need to convert the number of days in an amount of month and after to 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;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The last things is to finding out the date difference in years, months and days together and that 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;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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:
Home How To Use Oracle Date Format How To Use Oracle Interval

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*