SqlServer函数获取指定日期后的第某个工作日
获取工作日
需要编写一个SqlServer函数，F_getWorkday，传入两个参数，第一个为时间date，第二个参数为第几个工作日num。调用F_getWorkday后返回date之后的第num个工作日。
现有节假日安排表如下：
1 drop function [dbo].[F_getWorkday]; 2 create function [dbo].[F_getWorkday]( 3 @staDate datetime, 4 @workDay int 5 ) 6 returns datetime 7 as 8 begin 9 declare @curDay int ; 10 declare @tempDay datetime; 11 declare @weekday int; 12 declare @endDate datetime; 13 set @curDay=1; 14 select @tempDay = dateadd(day,1,@staDate); 15 while @curDay <=@workDay 16 begin 17 declare @tempDay2 datetime; --没有放假 18 if(select COUNT(1) from dategrid where set_type=1 and begin_date<=@tempDay and end_date>=@tempDay)<=0 19 begin--没加班 20 if(select COUNT(1) from dategrid where set_type=2 and begin_date<=@tempDay and end_date>=@tempDay)<=0 21 begin 22 select @weekday = datepart(weekday, @tempDay); 23 if (@weekday=1)--星期天 24 begin 25 select @tempDay = dateadd(day,1,@tempDay); 26 end 27 else if(@weekday>=2 and @weekday<=6)--周一到周五 28 begin 29 set @endDate = @tempDay; 30 select @tempDay = dateadd(day,1,@tempDay); 31 set @curDay = @curDay + 1; 32 end 33 else--周六 34 begin 35 select @tempDay = dateadd(day,2,@tempDay); 36 end; 37 end 38 else--加班 39 begin 40 set @tempDay2 = null; 41 select @tempDay2 = end_date from dategrid where set_type=2 and begin_date<=@tempDay and end_date>=@tempDay;--@tempDay在加班中的最后一天 42 set @endDate = @tempDay2; 43 select @tempDay = dateadd(day,1,@tempDay2); 44 set @curDay = @curDay + 1; 45 end; 46 end 47 else--放假 48 begin 49 select @tempDay2 = end_date from dategrid where set_type=1 and begin_date<=@tempDay and end_date>=@tempDay;--@tempDay在放假中的最后一天 50 select @tempDay = dateadd(day,1,@tempDay2); 51 set @endDate =@tempDay;--最后一天在假期情况 52 set @curDay = @curDay + 1;--叠加器加一次 53 end; 54 end; 55 return @endDate; 56 end
