oracle - How to get business days or hours between two dates

Question

How can I calculate business hours or days between two dates in oracle 10g?

For example we have two dates; 14/08/2012 9:30 and 16/08/2012 12:00 And we have working hours 09:30 to 18:30 in weekdays.

How can I calculate working hours or days excluding National Holidays, Sat & Sun with oracle 10g?

Answer 1

You can't. It's as simple as that. National holidays vary the world over, they vary year by year and extra ones can be added or taken away at any time. Additionally some jurisdictions carry over national holidays that fall on the weekend and have them the next week; others don't.

You'll need to create a calender table and flag national holidays / weekends etc in this.

For instance

create table calender
  ( day date
  , weekend varchar2(1)
  , holiday varchar2(1)
    );

Then insert some data into it...

 insert into calender (day, weekend)
 select trunc(sysdate + level)
      , case when to_date(sysdate + level,'fmDAY') in ('SATURDAY','SUNDAY') 
                  then 'Y' else 'N' end
   from dual
connect by level <= 365

Lastly, manually update what you count as a national holiday in there.

You can then select working days, depending on how you populated it with something like this:

select count(*)
  from calender
 where day between :startdate and :enddate
   and weekend = 'N'
   and holiday = 'N'