I have tested all of the solutions proposed here and none of them work.
Here are some test scenarios that broke a lot of the above solutions.
(assuming Saturday and Sunday are the days you are excluding):
-Add 0 days to a Saturday - Expected result = Saturday
-Add 0 days to a Sunday - Expected result = Sunday
-Add 1 day to Friday - Expected result = the following Monday
-Add 1 day to Saturday - Expected result = the following Monday
-Add 1 day to Sunday - Expected result = the following Monday
-Add 3 days to Friday - Expected result = the following Wednesday
-Add 5 days to Saturday - Expected result = the following Friday
-Add 5 days to Friday - Expected result = the following Friday
-Subtract 1 day from Monday - Expected result = the previous Friday
-Subtract 1 day from Sunday - Expected result = the previous Friday
-Subtract 1 day from Saturday - Expected result = the previous Friday
-Subtract 3 days from Monday - Expected result = the previous Wednesday
-Subtract 5 days from Saturday - Expected result = the previous Monday
-Subtract 5 days from Monday - Expected result = the previous Monday
Here is what I wrote after reading this entire thread and picking the good pieces of logic:
CREATE FUNCTION [dbo].[BusinessDateAdd]
(
@FromDate DATE
,@DaysToAdd INT
)
RETURNS DATE
AS
BEGIN
--If there are no days to add or subtract, return the day that was passed in
IF @DaysToAdd = 0 RETURN @FromDate
DECLARE @Weeks INT
DECLARE @DMod INT
DECLARE @FromDateIndex INT
--number of weeks
SET @Weeks = @DaysToAdd/5
--remainder of days
SET @dmod = @DaysToAdd%5
--Get the FromDate day of the week, this logic standardizes the @@DateFirst to Sunday = 1
SET @FromDateIndex = (DATEPART(weekday, @FromDate) + @@DATEFIRST - 1) % 7 + 1
/*Splitting the addition vs subtraction logic for readability*/
--Adding business days
IF @DaysToAdd > 0
BEGIN
--If the FromDate is on a weekend, move it to the previous Friday
IF @FromDateIndex IN(1,7)
BEGIN
SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN -2 WHEN 7 THEN -1 END,@FromDate)
SET @FromDateIndex = 6
END
SET @FromDate = DATEADD(dd,
CASE
--If the mod goes through the weekend, add 2 days to account for it
WHEN
((@FromDateIndex = 3 --Tuesday
AND @dmod > 3) --Days until Friday
OR
(@FromDateIndex = 4 --Wednesday
AND @dmod > 2)--Days until Friday
OR
(@FromDateIndex = 5 --Thursday
AND @dmod > 1)--Days until Friday
OR
(@FromDateIndex = 6 --Friday
AND @dmod > 0))--Days until Friday
THEN
@DMod+2
--Otherwise just add the mod
ELSE
@DMod
END, @FromDate)
END
--Subtracting business days
IF @DaysToAdd < 0
BEGIN
--If the FromDate is on a weekend, move it to the next Monday
IF @FromDateIndex IN(1,7)
BEGIN
SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN 1 WHEN 7 THEN 2 END,@FromDate)
SET @FromDateIndex = 2
END
SET @FromDate = DATEADD(dd,
CASE
--If the mod goes through the weekend, subtract 2 days to account for it
WHEN
((@FromDateIndex = 5 --Thursday
AND @dmod < -3) --Days until Monday
OR
(@FromDateIndex = 4 --Wednesday
AND @dmod < -2)--Days until Monday
OR
(@FromDateIndex = 3 --Tuesday
AND @dmod < -1)--Days until Monday
OR
(@FromDateIndex = 2 --Monday
AND @dmod < 0))--Days until Monday
THEN
@DMod-2
--Otherwise just subtract the mod
ELSE
@DMod
END, @FromDate)
END
--Shift the date by the number of weeks
SET @FromDate = DATEADD(ww,@Weeks,@FromDate)
RETURN @FromDate
END