1.说明：去掉法定节假日（周六，周天）和指定节假日
2.创建表脚本：
代码 1 CREATE TABLE [dbo].[Holiday] 2 ( 3 ID varchar(36) NOT NULL CONSTRAINT DF_Holiday_ID DEFAULT (newid()), 4 Name varchar(50) NOT NULL, 5 BeginDate date NOT NULL, 6 EndDate date NOT NULL, 7 Flag bit NOT NULL, 8 CONSTRAINT PK_Holiday PRIMARY KEY CLUSTERED (ID) 9 )10 GO11 12 13 EXEC sp_addextendedproperty 'Name','节假日表','user','dbo','table','Holiday'14 GO15 16 EXEC sp_addextendedproperty 'MS_Description','ID','user','dbo','table','Holiday','column','ID'17 GO18 19 EXEC sp_addextendedproperty 'MS_Description','名称','user','dbo','table','Holiday','column','Name'20 GO21 22 EXEC sp_addextendedproperty 'MS_Description','开始时间','user','dbo','table','Holiday','column','BeginDate'23 GO24 25 EXEC sp_addextendedproperty 'MS_Description','结束时间','user','dbo','table','Holiday','column','EndDate'26 GO27 28 EXEC sp_addextendedproperty 'MS_Description','标识，0表示调班，1表示节假日','user','dbo','table','Holiday','column','Flag'29 GO
3.计算两个给定日期之间的实际工作日天数
代码 1 IF OBJECT_ID (N'[dbo].[fWorkdayDiff]',N'FN') IS NOT NULL 2 DROP FUNCTION [dbo].[fWorkdayDiff] 3 GO 4 5 CREATE FUNCTION [dbo].[fWorkdayDiff](@BeginDate date,@EndDate date) 6 RETURNS int 7 AS 8 BEGIN 9 DECLARE @TempDate date,@Count int,@Flag bit10 IF @BeginDate>@EndDate11 SELECT @Flag=1,@TempDate=@BeginDate,@BeginDate=@EndDate,@EndDate=@TempDate12 ELSE13 SET @Flag=014 SET @Count=015 SET @BeginDate=DATEADD(d,1,@BeginDate)16 WHILE @BeginDate<=@EndDate17 BEGIN18 SELECT @Count=CASE WHEN(((@@DATEFIRST+DATEPART(weekday,@BeginDate)-1)%7 BETWEEN 1 AND 5 AND 19 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE Flag=1 AND BeginDate<=@BeginDate AND @BeginDate<=EndDate)=0) OR20 ((@@DATEFIRST+DATEPART(weekday,@BeginDate)-1)%7 IN (0,6) AND 21 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE Flag=0 AND BeginDate<=@BeginDate AND @BeginDate<=EndDate)>0)) 22 THEN @Count+1 ELSE @Count END,@BeginDate=DATEADD(d,1,@BeginDate)23 END24 RETURN (CASE WHEN @Flag=1 THEN -@Count ELSE @Count END)25 END
4.计算给定日期加上若干工作日天数
代码 1 IF OBJECT_ID (N'[dbo].[fWorkdayAdd]',N'FN') IS NOT NULL 2 DROP FUNCTION [dbo].[fWorkdayAdd] 3 GO 4 5 CREATE FUNCTION [dbo].[fWorkdayAdd](@BeginDate date,@Count int) 6 RETURNS date 7 AS 8 BEGIN 9 DECLARE @EndDate date10 IF @Count>011 BEGIN12 SET @EndDate=DATEADD(d,1,@BeginDate)13 WHILE @Count>014 BEGIN15 SELECT @Count=CASE WHEN((16 (@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 BETWEEN 1 AND 5 AND 17 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=1 AND BeginDate<=@EndDate AND @EndDate<=EndDate)=0) OR18 ((@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 IN (0,6) AND 19 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=0 AND BeginDate<=@EndDate AND @EndDate<=EndDate)>0)) 20 THEN @Count-1 ELSE @Count END21 IF @Count=022 BEGIN23 SELECT @EndDate=@EndDate24 END25 ELSE 26 BEGIN27 SELECT @EndDate=DATEADD(d,1,@EndDate)28 END29 END30 END31 ELSE32 BEGIN33 SET @EndDate=DATEADD(d,-1,@BeginDate)34 WHILE @Count>035 BEGIN36 SELECT @Count=CASE WHEN((37 (@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 BETWEEN 1 AND 5 AND 38 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=1 AND BeginDate<=@EndDate AND @EndDate<=EndDate)=0) OR39 ((@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 IN (0,6) AND 40 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=0 AND BeginDate<=@EndDate AND @EndDate<=EndDate)>0)) 41 THEN @Count+1 ELSE @Count END42 IF @Count=043 BEGIN44 SELECT @EndDate=@EndDate45 END46 ELSE 47 BEGIN48 SELECT @EndDate=DATEADD(d,-1,@EndDate)49 END50 END51 END52 RETURN @EndDate53 END