如何获得两个日期之间的工作日或营业时间 [英] How to get business days or hours between two dates

问题描述

如何在oracle 10g中计算两个日期之间的营业时间或天数?

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

例如，我们有两个日期； 14/08/2012 9:30和16/08/2012 12:00我们在工作日的工作时间为09:30至18:30.

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.

我如何计算工作时间或工作日(国家法定节假日，周六和节假日除外)? Sun与Oracle 10g?

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

推荐答案

您不能.就这么简单.世界各地的国定假日不同，它们每年都在变化，可以随时添加或删除额外的假日.此外，一些司法管辖区还保留了周末的国定假日，并在下个星期放假.其他人没有.

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.

例如

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'

这篇关于如何获得两个日期之间的工作日或营业时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！