在oracle sql中添加工作日 [英] adding business days in oracle sql

问题描述

我有两个日期字段，DATE_FIELD_ONE = 8/30/2018和DATE_FIELD_TWO = DATE_FIELD_ONE +20.如果我仅添加20个工作日，我需要查找DATE_FIELD_TWO应该是什么.我将如何完成?我以为可能尝试"DY"，但不确定如何使它工作.谢谢.

I have two date fields, DATE_FIELD_ONE = 8/30/2018 and DATE_FIELD_TWO = DATE_FIELD_ONE + 20. I need to find what DATE_FIELD_TWO should be if I'm only added 20 business days . How would I accomplish this? I thought maybe trying 'DY' but not sure how to get it to work. Thanks.

CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SAT' THEN 1 ELSE 0 END
CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SUN' THEN 1 ELSE 0 END

推荐答案

您可以尝试以下方法:

select max(date_field_two) as date_field_two
  from
 (
 select date'2018-08-30'+  
    cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH') 
                                              in ('6','7') then 
            0
          else
            level
          end as int) as date_field_two, 
 sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')  
                                               in ('6','7') then 
            0
          else
            1
          end as int)) over (order by level) as next_day
      from dual
    connect by level <= 20*1.5 
-- 20 is the day to be added, every time 5(#of business days)*1.5 > 7(#of week days)
-- 7=5+2<5+(5/2)=5*(1+1/2)=5*1.5 [where 1.5 is just a coefficient might be replaced a greater one like 2]
-- so 4*5*1.5=20*1.5 > 4*7 
  )    
 where next_day = 20;

 DATE_FIELD_TWO
-----------------
   27.09.2018

通过使用connect by dual子句.

P.S.忽略公共假期的情况，公共假期因一种文化与另一种文化而异，具体取决于仅与周末有关的问题.

P.S. Ignored the case for public holidays, which differ from one culture to another , depending on the question being related with only weekends.

> Rextester演示

假设您在"2018-09-25"和"2018-09-26"(在此天数)中有国定假日，则考虑以下内容:

Edit : Assume you have a national holidays on '2018-09-25' and '2018-09-26' (in this set of days), then consider the following :

select max(date_field_two) as date_field_two
  from
 (
 select date'2018-08-30'+  
        (case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH') 
                                              in ('6','7') then 
               0
              when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
               0
              else
               level
              end) as date_field_two, 
 sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')  
                                               in ('6','7') then 
                0
               when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
                0 
               else
                1
               end as int)) over (order by level) as next_day
      from dual
    connect by level <= 20*2 
  )    
 where next_day = 20;

 DATE_FIELD_TWO
-----------------
   01.10.2018

它会在下一天进行迭代，在这种情况下，除非这个假期与周末重合.

which iterates one day next, as in this case, unless this holiday coincides with weekend.

这篇关于在oracle sql中添加工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！