Oracle DateDiff

In Oracle the date difference is simply calculated as difference of two date values. The query below can be used to compute work load estimates in terms of week days, manufacturing lead times, etc. The query shown below can be joined with company or organizational calendar to exclude holidays in work duration computation.

Note: When working with actual date values, TRUNC(start_date) may be required to remove the time part in the date if time has to be excluded.

Oracle Date Difference Query

WITH w_days AS
 (SELECT TO_DATE(&i_s_yyyymmdd,'yyyymmdd') start_date,
         TO_DATE(&i_e_yyyymmdd,'yyyymmdd') end_date
  FROM DUAL),
 w_positive AS
 (SELECT     '['|| TO_CHAR(start_date,'Mon dd, yyyy')||'] < ['||
     TO_CHAR(end_date,'Mon dd, yyyy')||']' as date_range_desc,
    SUM(DECODE(TO_CHAR(start_date+level,'DY'),'SAT',0,'SUN',0,1)) week_days,
    SUM(DECODE(TO_CHAR(start_date+level,'DY'),'SAT',1,'SUN',1,0)) weekend_days, 
    end_date-start_date date_diff,
    ROUND(MONTHS_BETWEEN(end_date,start_date),3) months
  FROM w_days
  WHERE end_date > start_date
  CONNECT BY level <= end_date-start_date
  GROUP BY start_date, end_date),
 w_negative AS
 (SELECT    '['||TO_CHAR(start_date,'Mon dd, yyyy')||'] > ['||
     TO_CHAR(end_date,'Mon dd, yyyy')||']' as date_range_desc,
    SUM(DECODE(TO_CHAR(end_date+level,'DY'),'SAT',0,'SUN',0,1)) week_days,
    SUM(DECODE(TO_CHAR(end_date+level,'DY'),'SAT',1,'SUN',1,0)) weekend_days, 
    end_date-start_date date_diff,
    ROUND(MONTHS_BETWEEN(end_date,start_date),3) months
  FROM w_days
  WHERE start_date > end_date
  CONNECT BY level <= start_date-end_date
  GROUP BY start_date, end_date)
SELECT * FROM w_positive UNION
SELECT * FROM w_negative;

-- Refer Date Diff ->  Query Output