SQL计算工作日

本文介绍两种实用的SQL技巧:一是计算两个日期间的工作日数量,二是生成当前月份的日历视图。前者利用巧妙的字符串操作解决了日期范围内的工作日计算问题,后者通过简单的SQL逻辑实现了日历格式的输出。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

本文摘录了两段SQL的小技巧,来解决工作中的一些常用到的问题。主要是为了记录一下第一条,这条SQL是用来计算两个日期中的工作日的方法,从ITPUT那边看来的,很实用的一段SQL,而且用的算法很好,值得学习。当然只能剔除掉周六周日,别的法定节假日肯定就不行了。另一个是写的如何打印日历,比较白痴的一段SQL,呵呵,顺手摘录一下。 
 
 
1、用SQL直接取工作日: 

 

with t as ( select date '2008-09-01' s, date '2008-09-10' e from dual) 

select s,e,e-s+ 1 total_days, 

(length( replace (rpad(substr( '0111110' ,to_char(s, 'd' )),e-s+ 1 , '0111110' ), '0' , '' ))) work_days 

from t; 

 

说明: 


1、其他没什么好说的,主要就是(length(replace(rpad(substr('0111110',to_char(s,'d')),e-s+1,'0111110'),'0',''))) 这句了,一层层看一下 
2、substr('0111110',to_char(s,'d')) 这句的作用是找出开始日为星期几,并将其后的每一天对应011110,0为休息日,1为工作日 

3、rpad(substr('0111110',to_char(s,'d')),e-s+1,'0111110') 将字符串填充的e-s+1的长度,且每个工作日均对应为1,休息日为0 

4、replace 的作用是把011111011111011111……中的0都删除,留下1 

5、最后用length 统计一下字符串的长度即为工作日的天数 

6、这个方法虽然简单,但是有一个很大的问题,就是Oracle的字符串最长只有4000个字符,也就是说,相隔时间大于4000天的,work_days 将恒等于2858而发生错误,所以需要进行进一步的改进 

 

 

with t as (selectdate'2008-09-01' s,date'2008-09-10' e from dual ) 

select s,e,e-s+ 1 total_days, 

trunc((e-s+ 1 )/ 7 )* 5 + nvl(length( replace (substr( '01111100111110' ,to_char(s, 'd' ), mod (e-s+ 1 , 7 )), '0' , '' )), 0 ) work_days 

from t; 


 

说明: 

1、使用了trunc((e-s+1)/7)*5的形式,然后进行不整位的补足,就没有了字符串长度的限制 


2、当(e-s+1)是7的倍数时,显然工作日就是 ((e-s+1)/7)*5 
3、当(e-s+1)不被7整除是,可以理解为将最前面不能被7整除的部分mod(e-s+1,7)取出来,剩余的部分则正好为((e-s+1)/7)*5 

4、nvl(length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')),0)就是对mod(e-s+1,7)部分的操作 

5、接下来这部分跟前一种方法的原理差不多,就是根据起始位置是星期几,来顺次往后取到工作日标记为1,休息日标记为0 

6、需要注意的是substr('0111110',n,0)结果为null,replace('0','0','')的结果也为null,此时整个修正不为结果为null,无法正确显示结果,好在这两种情况下后面部分的结果都是可以忽略不记的,所以在最外面套一层nvl就可以了。 

 

 
2、打印一张当月的日历: 
  
select max (decode( mod (n, 7 ), 0 , m, null )) 日 , 

       max (decode( mod (n, 7 ), 1 , m, null )) 一 , 

       max (decode( mod (n, 7 ), 2 , m, null )) 二 , 

       max (decode( mod (n, 7 ), 3 , m, null )) 三 , 

       max (decode( mod (n, 7 ), 4 , m, null )) 四 , 

       max (decode( mod (n, 7 ), 5 , m, null )) 五 , 

       max (decode( mod (n, 7 ), 6 , m, null )) 六 

  from ( select rownum m, rownum + to_char(trunc( sysdate , 'mm' ), 'd' ) - 2 n 

          from dual 

        connect by rownum < = to_char(last_day( sysdate ), 'dd' )) 

  group by trunc(n / 7 ) 

  order by trunc(n / 7 ); 


 

说明: 

1、首先通过 connect by rownum < = to_char(last_day( sysdate ), 'dd' ) 来取出当月的天数序列 

2、再用 to_char(trunc(sysdate, 'mm'), 'd')-2 来提取当天是对应星期几 

3、通过 decode( mod (n, 7 ), 0 , m, null ) 来确定该天打印在星期几的列中,其实mod放到第2步做思路更加清晰 

4、通过 group by trunc(n / 7 ) 来将一个星期内的天数打印到一行中 

5、虽然比较简单,而且也没有什么实用价值,不过这个小程序还是有一些闪光点的,比如group by,记录一下 




http://www.blogjava.net/decode360/archive/2008/08/30/282985.html

<think>好的,我现在需要解决用户的问题:如何用SQL计算两个日期之间的工作日天数。首先,我需要理解用户的需求,他们想要排除周末和可能的法定节假日,得到两个日期之间的实际工作日数。接下来,我得回顾一下相关的SQL知识,特别是日期函数和条件判断的应用。 首先,考虑基本的方法。通常,计算两个日期之间的自然日数可以用DATEDIFF函数,但工作日需要排除周六和周日。所以,可能需要生成这两个日期之间的所有日期,然后逐一检查是否为工作日。这里涉及到生成日期序列的方法,不同数据库可能有不同的实现方式,比如使用递归CTE或者数字辅助表。 然后,如何处理周末?每个日期可以通过DATENAME或DATEPART函数来判断星期几。例如,在SQL Server中,DATEPART(dw, date)返回星期几的数字,其中1是周日,7是周六。但需要注意,因为不同地区的设置可能会影响这个结果,所以可能需要调整。 接下来是法定节假日的问题。用户提到需要去除法定节假日,这意味着需要一个存储这些假日的表,例如“工作日”表,其中包含日期和是否为工作日的标志。这样,在计算时,除了排除周末,还要排除这些节假日。需要将生成的日期与该表进行连接,并筛选出工作日。 现在,考虑具体的实现步骤。首先,生成开始日期和结束日期之间的所有日期。这可以通过递归公共表达式(CTE)来实现,例如在SQL Server中使用WITH RECURSIVE。然后,对每个日期判断是否为周末,或者是否存在于节假日表中。最后,统计满足条件的工作日数量。 还需要考虑函数的创建。用户提供的引用中有存储函数的例子,比如使用CREATE FUNCTION来封装逻辑,这样可以在查询中方便地调用。例如,函数接收开始和结束日期,返回工作日的数量。 可能遇到的问题包括性能问题,尤其是当日期范围很大时,递归CTE可能导致效率低下。这时候,可以考虑使用数字辅助表来生成日期序列,或者优化查询条件,减少不必要的计算。 另外,不同数据库系统的语法可能不同。用户提供的引用中有SQL Server和可能其他数据库的例子,我需要确保给出的方法适用于用户所使用的数据库,但用户没有明确说明,所以可能需要给出通用的方法,并注明不同数据库的差异。 最后,测试函数是否正确。例如,给定一个已知的日期范围,手动计算工作日数,然后与函数的结果对比,确保逻辑正确,包括处理开始和结束日期当天的包含问题。 总结步骤: 1. 生成日期范围的所有日期。 2. 排除周末(周六和周日)。 3. 排除法定节假日(需要关联节假日表)。 4. 统计剩余的天数。 现在根据这些步骤,构建SQL查询或函数。同时,参考用户提供的引用中的方法,整合他们的解决方案,例如引用[2]中的函数使用了COUNT和BETWEEN条件,直接查询工作日表,这可能假设工作日表中已经预先处理了所有日期的工作日标志,包括排除周末和节假日。因此,如果用户有维护这样的表,可以直接使用引用中的方法。否则,需要结合动态判断周末和查询节假日表。 可能需要两种方法:一种依赖于预存的工作日表,另一种动态计算周末,并结合节假日表。根据用户的引用,他们可能更倾向于使用存储节假日的方式,所以需要说明这两种情况。 在编写示例时,应该包括两种情况:仅排除周末,以及同时排除周末和节假日。这样用户可以根据自己的需求选择适合的方法。同时,注意函数的创建语法,比如在SQL Server中使用CREATE FUNCTION,并处理参数和返回类型。 最后,确保代码语法正确,例如使用正确的日期函数,递归CTE的结构正确,以及连接条件的准确性。同时,提醒用户注意节假日表的维护,确保数据的准确性。</think>### 如何用 SQL 计算两个日期之间的工作日天数? 要计算两个日期之间的工作日天数,需排除周末(周六、周日)和法定节假日。以下是两种常见实现方法: --- #### **方法一:仅排除周末** 假设无需考虑法定节假日,仅需过滤周六、周日。 **步骤**: 1. 生成日期范围内的所有日期。 2. 过滤掉周六、周日。 3. 统计剩余天数。 **示例(SQL Server)**: ```sql WITH DateRange AS ( SELECT @dt_begin AS Date UNION ALL SELECT DATEADD(DAY, 1, Date) FROM DateRange WHERE Date < @dt_end ) SELECT COUNT(*) AS Workdays FROM DateRange WHERE DATEPART(WEEKDAY, Date) NOT IN (1, 7); -- 假设周日=1,周六=7 ``` *注意:不同数据库的周几计算方式可能不同,需根据实际调整*[^4]。 --- #### **方法二:排除周末和法定节假日** 需依赖预存的节假日表(例如表名 `Workdays`,字段 `DATE` 存储日期,`DAY_WORK=1` 表示工作日)。 **步骤**: 1. 生成日期范围内的所有日期。 2. 关联节假日表,筛选 `DAY_WORK=1` 的日期。 **示例(存储函数)**: ```sql CREATE FUNCTION dbo.GetWorkdays(@BeginDate DATETIME, @EndDate DATETIME) RETURNS INT AS BEGIN RETURN ( SELECT COUNT(*) FROM Workdays WHERE DATE BETWEEN @BeginDate AND @EndDate AND DAY_WORK = 1 ); END ``` 调用方式: ```sql SELECT dbo.GetWorkdays('2023-01-01', '2023-12-31') AS TotalWorkdays; ``` *此方法要求节假日表已包含所有日期的工作日标记(自动排除周末和节假日)*[^2][^5]。 --- #### **注意事项** 1. **日期范围生成**:若日期跨度较大,递归 CTE 可能性能较差,可改用数字辅助表。 2. **周几计算**:不同数据库的 `DATEPART` 参数可能不同(如 MySQL 使用 `DAYOFWEEK`)。 3. **节假日表维护**:需定期更新法定节假日数据,确保准确性[^1][^3]。 --- ###
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值