T-SQL 获取两个日期之间的工作日数 [英] T-SQL get number of working days between 2 dates

问题描述

我想计算两个给定日期之间的工作日数.例如，如果我想计算 2013-01-10 和 2013-01-15 之间的工作日，结果必须是 3 个工作日(我不考虑该间隔的最后一天，我减去星期六和星期日).我有以下代码适用于大多数情况，除了我的示例中的代码.

I want to calculate the number of working days between 2 given dates. For example if I want to calculate the working days between 2013-01-10 and 2013-01-15, the result must be 3 working days (I don't take into consideration the last day in that interval and I subtract the Saturdays and Sundays). I have the following code that works for most of the cases, except the one in my example.

  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)

我怎样才能做到这一点?我是否必须经历所有的日子并检查它们?或者有什么简单的方法可以做到这一点.

How can I accomplish this? Do I have to go through all the days and check them? Or is there an easy way to do this.

推荐答案

请使用日历表.SQL Server 对国定假日、公司活动、自然灾害等一无所知.日历表相当容易构建，占用空间极小，如果引用足够多，就会在内存中.

Please, please, please use a calendar table. SQL Server doesn't know anything about national holidays, company events, natural disasters, etc. A calendar table is fairly easy to build, takes an extremely small amount of space, and will be in memory if it is referenced enough.

以下示例创建了一个日期为 30 年 (2000 -> 2029) 的日历表，但只需要 200 KB 的磁盘空间(如果使用页面压缩，则为 136 KB).这几乎可以保证少于在运行时处理某些 CTE 或其他设置所需的内存授权.

Here is an example that creates a calendar table with 30 years of dates (2000 -> 2029) but requires only 200 KB on disk (136 KB if you use page compression). That is almost guaranteed to be less than the memory grant required to process some CTE or other set at runtime.

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.

现在您要查询的查询非常简单:

Now the query you're after is quite simple to write:

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

有关日历表的更多信息:

More info on calendar tables:

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

关于无循环生成集的更多信息:

More info on generating sets without loops:

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

还要注意一些小事情，比如依赖 DATENAME 的英文输出.我已经看到一些应用程序因为某些用户的语言设置不同而中断，如果您依赖 WEEKDAY，请确保正确设置 DATEFIRST 设置...

Also beware of little things like relying on the English output of DATENAME. I've seen several applications break because some users had a different language setting, and if you're relying on WEEKDAY be sure you set your DATEFIRST setting appropriately...

这篇关于T-SQL 获取两个日期之间的工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！