Oracle : Date difference / Subtracting two dates

Example :-
Let us say that we have 2 date columns START_DATE and END_DATE

To find out the difference between 2 date columns/time taken

When you subtract two dates in Oracle you will get the following
( END_DATE - START_DATE ) = In Days
( END_DATE - START_DATE )*24 = In Hours
( END_DATE - START_DATE )*24*60 = In Minutes
( END_DATE - START_DATE )*24*60*60 = In Seconds

To find out the Month difference -

months_between(END_DATE, START_DATE) - Use function months_between

months_between () is Oracle function which will give you the number of months between the 2 dates which are passed to the function.


To delete particular number of days/months/hours from a date column

Use INTERVAL 'x' MONTH/DAY/YEAR - where x is number of months/days/years

To subtract 8 months from current date -
select sysdate - INTERVAL '8' MONTH from dual;

To add 5 days from the column START_DATE -
select START_DATE + INTERVAL '5' DAY from RUN_STATUS;



NUMTODSINTERVAL(n, 'day'/'hour'/'min'/'sec') converts n to an INTERVAL DAY TO SECOND literal

e.g NUMTODSINTERVAL(100, 'day')

NUMTOYMINTERVAL(n, 'year'/'month') converts n to an INTERVAL YEAR TO MONTH literal

e.g NUMTODSINTERVAL(1, 'year')