从特定日期减去营业日

I need to subtract a number of business days (1-15 days) from a specific date, for example subtracting 5 business days from 2013-12-27 should return 2013-12-20, is there an easy way to do that?

我需要从特定日期减去一些工作日（1-15天），例如从2013-12-27减去5个工作日应该返回2013-12-20，是否有一种简单的方法可以做到这一点？

3 个解决方案

#1

This post explains how to do it using a recursive CTE:

这篇文章解释了如何使用递归CTE来做到这一点：

Business Days Calc Using Recursive CTE

使用递归CTE的营业日计算

#2

One way to do that is to pre-create a table with all the dates for couple of years in advance and select from that table. This way you can mark saturdays, sundays, holidays, etc.

一种方法是预先创建一个包含几年所有日期的表，并从该表中选择。通过这种方式，您可以标记周六，周日，节假日等。

#3

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/10/01'
SET @EndDate = '2013/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 1 ELSE 0 END)
  -(CASE WHEN DATEPART(dw, @EndDate)   = 7 THEN 1 ELSE 0 END)
                                AS [TotalWorkingDays]

Result

结果

TotalWorkingDays
23

Important Note

重要的提示

This method will only ignore Saturdays and Sundays, If you want to exclude National Holidays and other seasonal Holidays you will need to make use of a calender table as Zdravko Danev has already mentioned.

这种方法只会忽略星期六和星期日。如果你想排除国定假日和其他季节性假期，你需要使用日历表，正如Zdravko Danev已经提到的那样。

#1