Adding Businessdays to a date

[ahbenshaut]

Greetings all!
Hopefully, I can get some help with this. I am trying to create/find a way to add business days to a specific date. I already have a sql function that will determine if a date is a holiday but I cannot figure out how to add business days to a date. Help!

 

[kulrom]

Greetings all!
Hopefully, I can get some help with this. I am trying to create/find a way to add business days to a specific date. I already have a sql function that will determine if a date is a holiday but I cannot figure out how to add business days to a date. Help!

If you got the way how to determine if a date is a holiday then the rest of the dates are business days ... i beleive you got the idea. IF someDate <> holiday then someDate = businessDay/s

Cheers

 

[Administrator]

Look at DayOfWeek and test that it's not your non-working days.

 

[DavidT_macktool]

DayofWeek = Int(someDate.DayOfWeek)
If DayofWeek < 6 Then
' it is a weekday
Else
'it is the weekend
End If

(6 & 7 are Saturday and Sunday)

 

[TechGnome]

I've done just this very thing in SQL. I'll post what I can of it.

CREATE FUNCTION fn_AddBusinessDays 
	(@MyDate datetime, -- This is the starting date
	 @NumOfDaysToAdd int) -- This is the number of business days to add to @MyDate

RETURNS datetime
AS
BEGIN
	DECLARE @Count int
	DECLARE @NewDate datetime

	-- DATEPART(dw, GETDATE()) -- Returns 1-7 indicating day of week: 1- Sunday; 7- Saturday
	
	SET @Count = @NumOfDaysToAdd
	SET @NewDate = @MyDate

	-- As long as we still have days to add...
	WHILE @Count > 0
	  BEGIN
		-- Go ahead and add a day
		SET @NewDate = DATEADD(d,1,@NewDate)
		-- If it lands on a weekend, loop until we come back around to Monday
		WHILE ((DATEPART(dw, @NewDate) = 7) OR (DATEPART(dw, @NewDate) = 1))
		  BEGIN
			SET @NewDate = DATEADD(d,1,@NewDate)		
		  END
		--Decriment our counter
		SET @Count = @Count - 1
	  END
	-- Return our new calculated date
	RETURN @NewDate

END
GO

Tg