Finding the Next Business Day Recursively

A recursive function for calculating next business day

Overview

The other day I found myself needing to come up with a way to calculate the next business day including taking into account holidays. A recursive function turned out to be just the thing to use.

Another challenge was handling different @@DATEFIRST settings. The problem was that in a user defined function you cannot use SET DATEFIRST. To get around this I used the Modulo function. I'll show you the scripts first and then go into the details.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function fnGetNextBusinessDay (@startDate smalldatetime,@numDays int)
returns smalldatetime as
Begin
Declare @nextBusDay smalldatetime
Declare @weekDay tinyInt
set @nextBusDay = @startDate
Declare @dayLoop int
set @dayLoop = 0
while @dayLoop < @numDays
Begin
set @nextBusDay = dateAdd(d,1,@nextBusDay) -- first get the raw next day
SET @weekDay =((@@dateFirst+datePart(dw,@nextBusDay)-2) % 7) + 1
-- always returns Mon=1 - can't use set datefirst in UDF
-- % is the Modulo operator which gives the remainder
-- of the dividend divided by the divisor (7)
-- this allows you to create repeating
-- sequences of numbers which go from 0 to 6
-- the -2 and +1 adjust the sequence start point (Monday) and initial value (1)
if @weekDay = 6 set @nextBusDay = @nextBusDay + 2 -- since day by day Saturday = jump to Monday
-- Holidays - function calls itself to find the next business day
select @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay,1)
where exists (select holidayDate from Holiday where holidayDate=@nextBusDay)
-- next day
set @dayLoop = @dayLoop + 1
End
return @nextBusDay
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Details

The first interesting thing about this script is the use of Modulo to make sure the function works no matter what @@DATEFIRST is set at. For those of you not familiar with Modulo, it gives the remainder of one number divided by another. So for example: 7 % 7 = 0, 9 % 7 = 2, 15 % 7 = 1 etc. I use this function all the time with Crystal reports to create a greenbar effect. If you take the record number modulo 2 you'll get 0 when it's even and 1 when it's odd. In this case, if you added the @@DATEFIRST value to the weekday value, it resulted in a sequence of numbers that was ripe to have modulo 7 applied to it.

From there, the next thing to do was subtract 2 from the @@DATEFIRST + the weekday value to start the sequence with 0 on Monday and finally add 1 to that value so that Monday was always 1. If you would like to explore modulo sequences, it's very easy to do using the MOD function in Microsoft Excel (this is what I did).

Now to the recursive part of this procedure. I had taken care of calculating the next business day accounting for weekends and began to work on the holidays part of the procedure. The first step was to check if the next business day was a holiday and therefore had an entry in the holiday table. If there was one, then I had to go to the next day. However it couldn't simply be the next day since the next day could also be a weekend or a holiday. It had to be the next business day. That's when the light went off. Have the function call itself! I just hard coded 1 as the number of days to look forward and then called the function.

Summary

The magic question with recursive functions is : "Do I have to do a calculation and then do the same calculation on the result?" If you ever find yourself asking that question, you've got a great candidate for a recursive function. The magic question with Modulo is "Do I have a repeating series of numbers?" If so, Modulo may be the answer. I hope you found this little piece interesting.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.