Date Arithmetic - Dierence between Dates in Days, Hours, Minutes and/or Seconds
In oracle, the difference (in days and/or fractions thereof) between two DATEs can be found using subtraction:
Outputs the number of days between the two dates:
Outputs the fraction of days between two dates:
The difference in hours, minutes or seconds can be found by multiplying this number by 24, 24*60 or 24*60*60 respectively.
The previous example can be changed to get the days, hours, minutes and seconds between two dates using:
(Note: TRUNC() is used rather than FLOOR() to correctly handle negative differences.)
The previous example can also be solved by converting the numeric difference to an interval using NUMTODSINTERVAL():