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