请使用日历表.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...