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