Transact-SQL User Function to Subtract Business Days

I came across the requirement of building a function that subtracts business days from a specific date value, the problem with this is you don’t know if a weekend will occur between those days, and if you are looking at more than 7 days then there could be more than one weekend.

The best way I could think of is to use a recursive CTE that keeps incrementing a counter as far as it’s not a weekend day until it reaches the specified number of days.

One thing you need to note about this method is because it uses a recursive CTE by default you will not be able to go over 70 days or so because the max recursion is 100 times so you will need to set the MAXRECURSION option to a number that is over the total of calendar days during that period, it should be added under the select statement like:

SELECT @ReturnDate = MIN(DayDate) FROM Dates

OPTION (MAXRECURSION 1000)

Here is the function:

CREATE FUNCTION fn_SubtractBusinessDays(@startDate date, @Days INT )

RETURNS DATE AS
BEGIN

DECLARE @ReturnDate DATE;

WITH Dates AS
(

SELECT @startDate AS BusinessDay, 0 AS DaysCount

 UNION ALL

 SELECT DATEADD(d,-1,BusinessDay) AS BusinessDay ,
DaysCount + (CASE WHEN DATEPART(dw, DATEADD(d,-1,BusinessDay)) NOT IN (7,1) THEN 1 ELSE 0 END) DaysCount
 FROM Dates
 WHERE DaysCount < @days

)

SELECT @ReturnDate = MIN(BusinessDay) FROM Dates

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