最近公司OA系统的需求，实现一年中每个日期剔除节假日和星期天之后的五个日期是几号，每个日期都要跳过节假日和星期天，当时是真的慌了，郁闷了一天，后来半夜忽然来灵感，想想还是可以实现。
需要做一张节假日的表，存入一年中的法定节假日，然后用游标循环日期，星期天可以用datename函数剔除：datename(dw,日期)!='Sunday'
1 ALTER PROCEDURE [dbo].[pc_Job_Insert_HolidayForUQ] 2 as 3 BEGIN TRY 4 5 --插入一年的日期 6 if not exists(select CONVERT(varchar(10),StartDate,23) as StartDate from [dbo].[HolidayForUQ] where year(StartDate)= year(getdate())+1) 7 begin 8 DECLARE @newdate varchar(4),@newdate1 varchar(4) 9 set @newdate=year(getdate()) 10 set @newdate1=year(getdate())+1 11 insert into [dbo].[HolidayForUQ] 12 select convert(varchar(10),riqi,120),'','','','','',GETDATE() from(select riqi=dateadd(dd,number,@newdate+'-12-31') from master..spt_values where type='p' 13 and number between 1 and 366) a where datepart(yy,a.riqi)=@newdate1 14 end 15 16 --创建临时表 17 CREATE TABLE #tab_HolidayForUQ( 18 ID int identity(1,1), 19 StartDate varchar(10) 20 ) 21 22 --剔除节假日和周日,插入临时表，可以得到ID 23 insert into #tab_HolidayForUQ select CONVERT(varchar(10),StartDate,23) as StartDate from [dbo].[HolidayForUQ] where datename(dw,StartDate)!='Sunday' 24 and StartDate not in(select convert(datetime,Holiday_Date,112) from vw_fmis_Holiday_3621 where Holiday_Date=StartDate) 25 order by StartDate asc 26 27 28 Declare @StartDate varchar(10) --定义循环的当前日期 29 DECLARE Menu CURSOR for 30 --------- 31 select CONVERT(varchar(10),StartDate,23) as StartDate from [dbo].[HolidayForUQ] --where StrandTime >='2022-01-01' --in('2018-10-01','2018-10-02') 32 -------- 33 open Menu 34 FETCH NEXT FROM Menu into @StartDate 35 while @@FETCH_STATUS =0 36 BEGIN 37 --------------------------------------------------------------------------------- 38 39 --select HFID from [dbo].[HolidayForUQ] 40 41 DECLARE @IsID int,@starttime varchar(10),@addto1 datetime,@addto2 datetime,@addto3 datetime,@addto4 datetime,@addto5 datetime,@No1 int 42 --链接临时表 43 select @starttime=CONVERT(varchar(10),a.StartDate,23),@IsID=b.ID from [dbo].[HolidayForUQ] a left join #tab_HolidayForUQ b 44 on a.StartDate=b.StartDate where a.StartDate=@StartDate 45 46 --select CONVERT(varchar(10),a.StartDate,23) as StartDate,b.ID from [dbo].[HolidayForUQ] a left join #tab_HolidayForUQ b 47 --on a.StartDate=b.StartDate 48 49 if(@IsID is not null)--如果当前日期不是节假日或者周日，就根据剔除了节假日和周日临时表数据的自增ID去添加 50 begin 51 select @addto1=StartDate from #tab_HolidayForUQ where ID=@IsID+1 52 select @addto2=StartDate from #tab_HolidayForUQ where ID=@IsID+2 53 select @addto3=StartDate from #tab_HolidayForUQ where ID=@IsID+3 54 select @addto4=StartDate from #tab_HolidayForUQ where ID=@IsID+4 55 select @addto5=StartDate from #tab_HolidayForUQ where ID=@IsID+5 56 update [dbo].[HolidayForUQ] set AddTo1=@addto1,AddTo2=@addto2,AddTo3=@addto3,AddTo4=@addto4,AddTo5=@addto5 where StartDate=@starttime 57 end else 58 begin 59 --如果当前日期是节假日或者周日，就查询临时表根据大于当前日期添加 60 select top 1 @No1=ID from #tab_HolidayForUQ where StartDate>@starttime order by StartDate asc 61 select @addto1=StartDate from #tab_HolidayForUQ where ID=@No1 62 select @addto2=StartDate from #tab_HolidayForUQ where ID=@No1+1 63 select @addto3=StartDate from #tab_HolidayForUQ where ID=@No1+2 64 select @addto4=StartDate from #tab_HolidayForUQ where ID=@No1+3 65 select @addto5=StartDate from #tab_HolidayForUQ where ID=@No1+4 66 update [dbo].[HolidayForUQ] set AddTo1=@addto1,AddTo2=@addto2,AddTo3=@addto3,AddTo4=@addto4,AddTo5=@addto5 where StartDate=@starttime 67 end 68 69 --------------------------------------------------------------------------------- 70 --next 71 FETCH NEXT FROM Menu 72 INTO @StartDate 73 END 74 CLOSE Menu 75 DEALLOCATE Menu 76 END TRY 77 --捕捉异常 78 BEGIN CATCH 79 SELECT 80 ERROR_NUMBER() as ErrorNumber, 81 ERROR_MESSAGE() as ErrorMessage 82 END CATCH;