CREATE FUNCTION [dbo].[fns_AddWorkingDays]
(
@StartDate datetime,
@N INT
)
RETURNS datetime
AS
BEGIN
-- This ensures that however the server is configured for dates
-- the function will know the DATEPART(DW values for Saturday
-- and Sunday
declare @SaturdayDW int
declare @SundayDW int
set @SaturdayDW = DATEPART(DW,CONVERT(datetime,'2019 January 5')) -- A Saturday
set @SundayDW = DATEPART(DW,CONVERT(datetime,'2019 January 6')) -- A Sunday
-----------------------------------------------------------------
-- If @N is zero then reduce the date by 1
-- and try adding one day
if @N=0
begin
set @N=1
set @StartDate=DATEADD(DAY,-1,@StartDate)
end
----------------------------------------------------------------
-- If @N >=0 then increment dates while counting
-- If @N =0 set @increment = 1 else set @increment = -1
-- Work out what to do if the loop encounters
-- a Saturday or Sunday - it depends on the
-- direction of travel.
declare @saturdayadjustment int
declare @sundayadjustment int
if @n>=0 set @saturdayAdjustment = 2 else set @saturdayAdjustment = -1
if @n>=0 set @sundayAdjustment = 1 else set @sundayAdjustment = -2
----------------------------------------------------------------
declare @CountDays int
set @CountDays=0
declare @LoopDate datetime
set @LoopDate = @StartDate
while @CountDays<ABS(@N)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
if DATEPART(DW,@LoopDate)= @SaturdayDW
begin
set @LoopDate=DATEADD(DAY,@saturdayAdjustment,@LoopDate)
end
if DATEPART(DW,@LoopDate)= @SundayDW
begin
set @LoopDate=DATEADD(DAY,@SundayAdjustment,@LoopDate)
end
if exists(select HolidayID from tblHoliday where HolidayDate=@LoopDate)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
end
set @CountDays=@CountDays+1
end
return @LoopDate

END