该函数意在计算给定两个日期间工作日天数,结束日期需大于开始日期(注意:在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