转：SQL Server的自定义函数：统计两日期之间工作日的数量

CREATE FUNCTION kmes.WorkDay_f(
@dt_begin   datetime,
@dt_end     datetime
)RETURNS int
AS
BEGIN
    DECLARE @workday int,@i int,@bz bit,@dt datetime

    set @dt_begin = convert(datetime, convert(nvarchar(32), @dt_begin, 23))
    set @dt_end = convert(datetime, convert(nvarchar(32), @dt_end, 23))

    IF @dt_begin>@dt_end
        SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
    ELSE
        SET @bz=0

    SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end),@workday=@i/7*5,@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)

    WHILE @dt_begin<@dt_end
    BEGIN
        SELECT @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 THEN @workday+1 ELSE @workday END,@dt_begin=@dt_begin+1
    END

    RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO

 

转载于:https://www.cnblogs.com/songsh96/archive/2013/03/21/2973678.html