工作日计算 [英] Business Days calculation

问题描述

我有一个查询，我通过以下 SQL 查询计算两天之间的总天数，包括开始和结束日期.如果结束日期不为空，则结束日期被视为当前日期.

I have a query where I am calculating total days between two days including start and end date by the following SQL query. If the end date is not null, then end date is considered as current date.

这个查询完成了这项工作.但我不想计算周六和周日.可能的英国公共假期.(我可以做这个，如果我能理解周六和周日的逻辑)

This query does the job. But I do not want to count Sat and Sundays. Possible public UK Holidays.(I can do this one, if I can get the logic for Saturdays and Sundays)

SELECT  DateDiff(day,DateADD(day,-1,StartDate),ISNULL(EndDate,getDate()))numberOfDays
        FROM <mytable>

如何只计算两个日期之间的工作日?

How do I count only weekdays between two dates?

谢谢

推荐答案

试试这个

SELECT  DateDiff(day,DateADD(day,-1,StartDate),ISNULL(EndDate,getDate())) - 
( CASE WHEN DATENAME(dw, StartDate) = 'Sunday' OR 
DATENAME(dw,ISNULL(EndDate,getDate())) = 'Sunday' THEN 1 ELSE 0 END)
- ( CASE WHEN DATENAME(dw, StartDate) = 'Saturday' OR 
     DATENAME(dw,ISNULL(EndDate,getDate())) = 'Saturday' THEN 1 ELSE 0 END)
numberOfDays         
FROM <mytable> 

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