计算给定两个日期间工作日

    该函数意在计算给定两个日期间工作日天数，结束日期需大于开始日期（注意：在SQL中DATEPART(DW,YYYY-MM-DD)  计算日期所在星期部分时，以星期一作为星期的第一天），具体实现如下：

IF OBJECT_ID('udfCalculateWorkdays')  IS NOT NULL
DROP FUNCTION udfCalculateWorkdays
GO

 

CREATE FUNCTION udfCalculateWorkdays
(    @begindate DATETIME,    -- 开始日期 
     @enddate DATETIME         -- 结束日期
)
RETURNS INT
AS
BEGIN

    DECLARE @workdays INT, @currentdate DATETIME
    SET @workdays = 0   
    SET @currentdate = @begindate   
     IF (DATEDIFF(D, @begindate, @enddate) < 0 OR @begindate IS NULL OR @enddate IS NULL OR @begindate = ''  OR @enddate = '')        
         SET @workdays = 0   
     ELSE    
     BEGIN

        WHILE (DATEDIFF(D, @currentdate, @enddate) >= 0)       
        BEGIN           
            IF (DATEPART(DW, @currentdate) = 1)               
                SET @currentdate = DATEADD(D, 1, @currentdate)           
            ELSE IF (DATEPART(DW, @currentdate) = 7)               
                 SET @currentdate = DATEADD(D, 2, @currentdate)               
            ELSE      
                BEGIN                
                    SET @currentdate = DATEADD(D, 1, @currentdate)               
                    SET @workdays = @workdays + 1           
                END        
        END

      END  

      RETURN (@workdays) 

END