T-SQL获取两个日期之间的工作日数

Question

我想计算两个给定日期之间的工作日数。例如，如果我要计算2013年1月10日至2013年1月15日之间的工作日，则结果必须为3个工作日（我没有考虑该间隔中的最后一天，因此我减去了周六和周日）。我有以下适用于大多数情况的代码，除了我的示例中的代码。

  SELECT (DATEDIFF(day, '2013-01-10', '2013-01-15')) 
    - (CASE WHEN DATENAME(weekday, '2013-01-10') = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DATENAME(weekday, DATEADD(day, -1, '2013-01-15')) = 'Saturday' THEN 1 ELSE 0 END)

我该怎么做？我需要整天检查一下吗？还是有一个简单的方法来做到这一点。

Answer 1

请，请使用日历表。SQL
Server对国定假日，公司活动，自然灾害等一无所知。日历表非常易于构建，占用的空间非常小，如果引用得足够多的话，它将存储在内存中。

这是一个创建具有30年日期（2000-> 2029）的日历表的示例，但是磁盘上仅需要200 KB（如果使用页面压缩，则需要136
KB）。这几乎可以保证小于运行时处理某些CTE或其他集所需的内存授权。

CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 12
  AND DAY(dt) = 25
  AND IsWorkDay = 1;

-- continue with other holidays, known company events, etc.

现在，您要查询的查询非常容易编写：

SELECT COUNT(*) FROM dbo.Calendar
  WHERE dt >= '20130110'
    AND dt <  '20130115'
    AND IsWorkDay = 1;

有关日历表的更多信息：

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-
should-i-consider-using-an-auxiliary-calendar-
table.html

有关生成无循环集的更多信息：

http://www.sqlperformance.com/tag/date-
ranges

还要提防小事，例如依靠的英语输出DATENAME。我已经看到有几个应用程序中断，因为某些用户使用了不同的语言设置，并且如果您要依靠的话，请WEEKDAY确保DATEFIRST正确设置了您的设置…