一个高手的SQL求工作天数的函数

 

CREATE FUNCTION dbo.fun_GetWorkDay
(
     @dt_begin   datetime,
     @dt_end   datetime
)    returns   int
AS
BEGIN
    DECLARE @workday int, @i int, @bz bit, @dt datetime
    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)+1,
           @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

执行这个自定函数： 

select   dbo.fun_GetWorkDay('2005-01-01','2005-01-31') 
我们可以得到如下的结果：

  drop   function  fun_GetWorkDay
  -----------    
  21