SQL Server Function to return date from known number of working days

At work we required a way to get a date in the future from a known date + x number of working days.  With help from google searches of other solutions  I wrote this function to take in a datetime and number of days which was looped through, incrementing the date a day at a time and  adding to the count by one if  if the date was neither a weekend day nor a bank holiday. The table tblNonWorkingDays is just a single field of dates that are bank holidays.

I’m sure there are easier ways and if someone knows of one I’d welcome any input but I like the simplicity of this…


FUNCTION [dbo].[f_WorkingDays] (@InputDate datetime, @Days int)
RETURNS DATETIME AS
BEGIN

DECLARE @Count AS INT
DECLARE @NonWorking AS INT
DECLARE @DateIncrement AS INT
DECLARE @DayNumber AS INT
DECLARE @ReturnDate AS DATETIME

SET @ReturnDate = @InputDate
SET @Count = 0

SET @DateIncrement = 1

IF @Days < 0
BEGIN
SET @DateIncrement = -1
END

WHILE @Count < ABS(@Days)
BEGIN
SET @ReturnDate = DATEADD(DAY, @DateIncrement, @ReturnDate)

— Check if Weekday
SET @DayNumber = DATEPART(WEEKDAY, @ReturnDate)
IF (@DayNumber > 1 AND @DayNumber < 7) –Sat or Sun
BEGIN

— if weekday then check if not a bank holiday

SELECT @NonWorking = (
SELECT COUNT(NonWorkingDate)
FROM [tblNonWorkingDates]
WHERE NonWorkingDate =
(SELECT CONVERT(VARCHAR(4), DATEPART(YEAR, @ReturnDate)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(MONTH, @ReturnDate)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(DAY, @ReturnDate)) + ‘ 00:00:00.000’))

IF @NonWorking = 0
BEGIN
SET @Count = @Count + 1
END

END

END –WHILE

RETURN @ReturnDate

END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s