Oracle 计算起始日期之间的工作日天数（目前只支持同一年）

需求

计算2020.4.13号到2020.8.6号之间总共多少个工作日（去除周末和节假日）。

解决办法

人工解决方法：

用手机看着日历数天数累加，哈哈

自动计算方法：

SQL自动计算

如下是计算函数：

create or replace function count_workdays(
       startdate date,
       enddate date,
       is_clude_weekend int --是否包括特殊日期-周末的工作日  0|不包括，1|包括
) 
       return varchar2 
is
       c_year      int;              --计算的年份
       s_month     int;              --开始月份
       e_month     int;              --结束月份
       s_day       int;              --开始的天数
       e_day       int;              --结束的天数
       l_day       date;             --某月的最后一天
       c_f_day     date;             --某月开始计算的第一天
       diff_day    int;              --相差天数
       ct_total    int := 0;         --计算出的总天数
       ct_workday  int := 0;         --计算出的工作日天数（不包括周末、节假日）
       str_info    varchar2(32767);  --范围内的工作日拼接
       str_month   varchar2(10000);  --范围内的月份拼接
       is_holiday  int;              --是否是节假日 
       is_spec_workday int;          --是否为特殊工作日(周末的工作日)
       i_holiday   int;              --临时接收节假日变量 
       i_spec_workday int;           --临时接收特殊工作日变量
       str_holiday  varchar2(10000); --范围内的节假日拼接 
begin
   --将月份、天数赋值到临时变量
   s_month   := extract(month from startdate);
   s_day     := extract(day from startdate);
   e_month   := extract(month from enddate);
   e_day     := extract(day from enddate);
   
   --判断日期是否顺序错误
   if startdate > enddate then
      return '开始日期不允许大于结束日期';
   end if;
   --暂时不考虑跨年的情况
   if extract(year from startdate) != extract(year from enddate) then
     return '目前暂不支持跨年计算';
   else
     c_year := extract(year from startdate);
   end if;  
   
   
   --循环每个月
   FOR m in 0..(e_month - s_month) LOOP 
      str_month := str_month ||'【'|| (s_month + m)|| '】 ';
      --dbms_output.put_line('当前月份 = '||(s_month + m));
      
      select last_day(to_date(c_year||lpad(s_month + m,2,0)||'01','yyyymmdd')) into l_day from dual;
      --根据当月不同，计算不同的相差天数值
      if s_month < e_month then  
         select case when m = 0 then
                   l_day - startdate   --开始日期所在月份最后一天 - 开始日期
                when s_month + m = e_month then
                   enddate - (add_months(last_day(enddate),-1) + 1)  --结束日期 - 结束日期所在月份第一天
                else
                   l_day - (add_months(l_day,-1) + 1)   --该月最后一天 - 该月第一天
                end ,--相差天数
                case when m = 0 then
                   startdate   --开始日期所在月份的第一天
                when s_month + m = e_month then
                   add_months(last_day(enddate),-1) + 1  --结束日期所在月份的第一天
                else
                   add_months(l_day,-1) + 1   --该月份第一天 
                end --该月份第一天                   
                into diff_day , c_f_day
         from dual;         
      else
         select e_day - s_day,startdate into diff_day,c_f_day from dual;
      end if;
      
      --dbms_output.put_line('当前月份相差天数 = '||diff_day);
      if s_month + m = e_month then        
         dbms_output.put_line('当前月份起始日期 = '||to_char(enddate,'yyyymmdd') ||' - '||to_char(add_months(last_day(enddate),-1) + 1,'yyyymmdd') || ' |开始计算日期 = '||to_char(c_f_day,'yyyymmdd') );
      end if;
      --dbms_output.put_line('当前月份开始计算日期 = '||c_f_day);
      
      --循环每天
      for d in 0..diff_day loop
         --先判断的该日期是否为节假日
         select count(1),max(s_date) into is_holiday,i_holiday from JS_JJRJLB where s_type = 3 and s_date = to_number(to_char((c_f_day + d),'yyyymmdd'));          
         if is_holiday = 0 then 
             if to_char((c_f_day + d),'D') not in('1','7') then --不是周末
                    ct_workday :=  ct_workday + 1   ;
                    str_info := str_info||'| '||to_char((c_f_day + d),'yyyymmdd');                                  
              elsif(is_clude_weekend = 1) then  --是周末且需要计算周末中的工作日
                  --取出周末的工作日（在特殊日期表中查询）
                  select count(1),max(s_date) into is_spec_workday,i_spec_workday from JS_JJRJLB where s_type = 1 and s_date = to_number(to_char((c_f_day + d),'yyyymmdd'));          
                  if is_spec_workday > 0 then       
                          ct_workday :=  ct_workday + 1   ;
                          str_info := str_info||'| '||i_spec_workday; 
                   end if;  
                            
              end if;
          else
             --拼接范围内的节假日
             str_holiday := str_holiday||'- '||i_holiday ;
             end if; 
          ct_total := ct_total + 1; 
      end loop;
   END LOOP;

   return  '[total_days] = '||ct_total||' [workdays] = '||ct_workday ||' [符合要求的日期] = '||substr(str_info,2)||' [节假日] = '||substr(str_holiday,2);
end count_workdays;

排除节假日创建的-特殊日期表

create table JS_JJRJLB
(
  id     NUMBER(16) not null,
  s_date NUMBER(8),
  s_type NUMBER(12) --1|工作日 2|周末 3|节假日
)

 测试验证

 

select count_workdays(to_date('20200413','yyyy-mm-dd'),to_date('20200806','yyyymmdd'),0) from dual;

查询结果如下：

 

后续会添加跨年计算的功能 ...

 

因为第一次写这种函数，可能有些地方逻辑写复杂了，希望有简便方法的前辈们不吝赐教，也欢迎发现问题的小伙伴在留言区留言，一起讨论学习，共同进步（找不到功能栏里面的小猴子表情了，就用符号代替啦 ^_^）