SQL返回在日期之间传递的工作日数之间 [英] SQL to return the number of working days between 2 passed in dates
本文介绍了SQL返回在日期之间传递的工作日数之间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要编写一个sql查询,返回两个给定日期之间的工作日数(周一至周五)。
I need to write an sql query that returns the number of Working days (Monday - Friday) between two given dates.
我想知道最多的是什么
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.
任何想法?
推荐答案
一个简单的方法来计算2个日期之间的工作日数量是:
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
这篇关于SQL返回在日期之间传递的工作日数之间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文