9. Working day processing function (standard holiday)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', CREATE function f_WorkDay(@dt_begin datetime, @dt_begin datetime); -- Calculation start date @dt_end dateTime -- calculation end date)RETURNS int AS BEGIN DECLARE @workday int,@i int,@bz bit,@dt datetime IF @dt_begin>@dt_end SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt ELSE SET @bz=0 SELECT @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 BEGIN SELECT @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 THEN @workday+1 ELSE @workday END, @dt_begin=@dt_begin+1 END RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END) END GO /*=================================================================*/ if exists (select * from dbo.sysobjects where id =  object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', CREATE function f_WorkDayADD(@date dateTime, @date datetime, @date datetime) RETURNS datetime AS BEGIN DECLARE @bz int -- The number of days to be added SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END,@date=DATEADD(Week,@workday/5,@date), @Workday =@workday%5 @date=DATEADD(Day,@bz,@date), @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5 THEN @workday-@bz ELSE @workday END WHILE (@@datefirst +DATEPART(Weekday,@date)-1)%7 in(0,6) SET @date=DATEADD(Day,@bz,@date) RETURN(@date) ENDCopy the code