CREATE FUNCTION GetWorkDays(@dt_begin DATETIME , --计算的开始日期@dt_end DATETIME--计算的结束日期)RETURNS INTAS BEGINDECLARE @workday INT ,@i INT ,@bz BIT ,@dt DATETIMEIF @dt_begin > @dt_end SELECT @bz = 1 ,@dt = @dt_begin ,@dt_begin = @dt_end ,@dt_end = @dtELSE SET @bz = 0SELECT @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 BEGINSELECT @workday = CASE WHEN ( @@DATEFIRST + DATEPART(Weekday, @dt_begin) - 1 )% 7 BETWEEN 1 AND 5 THEN @workday + 1ELSE @workdayEND ,@dt_begin = @dt_begin + 1ENDRETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)ENDGO--测试示例SELECT dbo.GetWorkDays('-08-1', '-08-31') AS '工作日'
200字范文,内容丰富有趣,生活中的好帮手!