Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...