# Finding the Next Business Day Recursively

By Rob Scholl, 2005/11/03

By Robert Scholl

### 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.

### Scripts

To start with, you'll need to create a table to hold the holidays:

CREATE TABLE [holiday] (
[holidayDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_holidayDate] PRIMARY KEY  CLUSTERED
(
[holidayDate]
)
)

Next you'll need to create the function:

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

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.

Here's a chart of the numbers:

 @@DATEFIRST plus Weekday 1 2 3 4 5 6 7 Monday 2 9 9 9 9 9 9 Tuesday 3 3 10 10 10 10 10 Wednesday 4 4 4 11 11 11 11 Thursday 5 5 5 5 12 12 12 Friday 6 6 6 6 6 13 13 Saturday 7 7 7 7 7 7 14 Sunday 8 8 8 8 8 8 8

Taking (@@DATEFIRST + the weekday value) % 7 always returns the following sequence:

 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 0 Sunday 1

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.

Total article views: 12782 | Views in the last 30 days: 7

Related Articles
FORUM

### SET DATEFIRST SQL Server

How to change datefirst for SQL Server...

ARTICLE

### Finding the Correct Weekday Regardless of DateFirst

In this article, learn how to get the correct weekday regardless of the DateFirst setting on your se...

FORUM

### Holidays

I need to find a way to skip holidays for a scheduled job.

SCRIPT

### Format Phone Number function

Function to format phone numbers entered as text.

FORUM

### SET DATEFIRST option

Behavioural Difference of SET DATEFIRST option in SQL 2000 and SQL 2005

Tags

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

#### 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.