SQL计算两个日期之间的工作天数

if exists ( select * from dbo.sysobjects where id = object_id (N ' [tb_Holiday] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ tb_Holiday ]
GO

-- 定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered , -- 节假日期
Name nvarchar ( 50 ) not null ) -- 假日名称
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDay] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_WorkDay ]
GO

-- 计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay
( @dt_begin datetime , -- 计算的开始日期
@dt_end datetime -- 计算的结束日期 )
RETURNS int
AS
BEGIN
IF @dt_begin > @dt_end
RETURN ( DATEDIFF ( Day , @dt_begin , @dt_end ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_begin AND @dt_end ))
RETURN ( - ( DATEDIFF ( Day , @dt_end , @dt_begin ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_end AND @dt_begin )))
END
GO

  if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDayADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_WorkDayADD ]
GO

-- 在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date datetime , -- 基础日期
@workday int -- 要增加的工作日数 )
RETURNS datetime
AS

BEGIN
IF @workday > 0
WHILE @workday > 0
SELECT @date = @date + @workday , @workday = count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday
ELSE WHILE @workday < 0
SELECT @date = @date + @workday , @workday =- count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday
RETURN ( @date )
END