SQL 返回两个传入日期之间的工作日数

2021-12-24 00:00:00 sql date oracle datediff plsql

我需要编写一个 sql 查询，返回两个给定日期之间的工作日(周一至周五)数.

I need to write an sql query that returns the number of Working days (Monday - Friday) between two given dates.

我想知道最有效的方法是什么?

I was wondering what would be the most efficient way to do this?

SELECT           --Start with total number of days including weekends             
(DATEDIFF(dd,@StartDate,@EndDate)+1) --Subtact 2 days for each full weekend 
(DATEDIFF(wk,@StartDate,@EndDate)*2) --If StartDate is a Sunday, Subtract 1          
ELSE 0               END)            --If EndDate is a Saturday, Subtract 1 
FROM dual

然后，能够从该计数中删除假期(例如圣诞节和节礼日)也会很有帮助.

Then it would also be helpful to be able to remove holidays from this count such as christmas day and boxing day.

有什么想法吗?

推荐答案

计算两个日期之间的工作日数的简单方法是:

an easy way to calculate to number of weekdays between 2 dates is :

SELECT
date1,
date2,
((date2-date1)-2*FLOOR((date2-date1)/7)-DECODE(SIGN(TO_CHAR(date2,'D')-
    TO_CHAR(date1,'D')),-1,2,0)+DECODE(TO_CHAR(date1,'D'),7,1,0)-
    DECODE(TO_CHAR(date2,'D'),7,1,0))*24 as WorkDays
FROM
  tablename
ORDER BY date1,date2

相关文章