Add business days to date in SQL without loops

Question

I currently have a function in my SQL database that adds a certain amount of business days to a date, e.g. if you enter a date that is a Thursday and add two days, it will return the date of the following Monday. I'm not bothered about any holidays, only weekends are excluded.

The problem is that this is currently done using a while loop, and it appears to be massively slowing down the stored procedure that uses it while generating a table. Does anyone know if there is any way to perform this calculation without while loops or cursors?

Just for information, this is the current function:

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @toDate datetime
DECLARE @daysAdded integer

-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate

while @daysAdded <= @daysToAdd
begin
    -- add a day to the to date
    set @toDate = DateAdd(day, 1, @toDate)
    -- only move on a day if we've hit a week day
    if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
    begin
        set @daysAdded = @daysAdded + 1
    end
end

RETURN @toDate

END

Answer 1

This is better if anyone is looking for a TSQL solution. No loops, no tables, no case statements AND works with negatives. Can anyone beat that?

CREATE FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date));
RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

Answer 2

This answer has been significantly altered since it was accepted, since the original was wrong. I'm more confident in the new query though, and it doesn't depend on DATEFIRST

---

I think this should cover it:

declare @fromDate datetime
declare @daysToAdd int

select @fromDate = '20130123',@DaysToAdd = 4

declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')

;with Numbers as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
    select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
    select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
    from
    Split t
        left join
    Numbers n
        on
            t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck

We split the time to be added into a number of weeks and a number of days within a week. We then use a small numbers table to work out if adding those few days will result in us hitting a Saturday. If it does, then we need to add 2 more days onto the total.

Answer 3

This answers is based on @ElmerMiller's answer.

It fixes the negative value on Sunday comment from @FistOfFury

Negative values don't work if the date passed in is Sunday

And the DATEFIRST setting comment from @Damien_The_Unbeliever

But this one does assume a particular DATEFIRST setting (7), which some of the others don't need.

Now the corrected function

CREATE FUNCTION[dbo].[AddBusinessDays](@Date DATE,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT,@f INT,@DW INT;
SET @f=CAST(abs(1^SIGN(DATEPART(DW, @Date)-(7-@@DATEFIRST))) AS BIT)
SET @DW=DATEPART(DW,@Date)-(7-@@DATEFIRST)*(@f^1)+@@DATEFIRST*(@f&1)
SET @d=4-SIGN(@n)*(4-@DW);
RETURN DATEADD(D,@n+((ABS(@n)+(@d%(8+SIGN(@n)))-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

Answer 4

Building off of the answer that was accepted for this question, the following user-defined function (UDF) should work in all cases--regardless of the setting for @@DateFirst.

UPDATE: As comments below indicate, this function is designed for the FromDate to be a weekday. The behavior is undefined when a weekend day is passed in as the FromDate.

ALTER FUNCTION [dbo].[BusinessDaysDateAdd] 
(
   @FromDate datetime,
   @DaysToAdd int
)
RETURNS datetime
AS
BEGIN
   DECLARE @Result datetime

   SET @Result = DATEADD(day, (@DaysToAdd % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @FromDate) + (@DaysToAdd % 5)) % 7)
                                                 WHEN 0 THEN 2
                                                 WHEN 1 THEN 1
                                                 ELSE 0 END, DATEADD(week, (@DaysToAdd / 5), @FromDate))

   RETURN @Result
END

Answer 5

*I know this is an old thread but found something extremely useful a while ago, modified it and got this.

select ((DATEADD(d,DATEDIFF(d,0,(DATEADD (d,2,@fromDate))),@numbOfDays)))*

Update: I am sorry in a haste to find a piece of code (in a single statement) and to avoid using a function, I posted incorrect code here.

Bit mentioned above can be used if the number of days you are adding is 7 or less.

I have changed the code with required parameters for better understanding.

Anyway, I ended up using what 'Nate Cook' has mentioned above. And used it as a single line of code. (Because I am restraining from using functions)

Nate's code

select(
DATEADD(day, (@days % 5) + 
CASE ((@@DATEFIRST + DATEPART(weekday, GETDATE()) + (@days % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, DATEADD(week, (@days / 5), GETDATE()))
)

Answer 6

To expand on Amine's comment and Nate cook's answer above, the one-liner solution to this is:

declare @DaysToAdd int , @FromDate datetime 
set @DaysToAdd=-5      --5 days prior is 3/28/14
set @FromDate='4/4/14'
select 
    DATEADD(day, (@DaysToAdd % 5) 
    +   CASE 
        WHEN ((@@DATEFIRST + DATEPART(weekday, @FromDate)) % 7 + (@DaysToAdd % 5)) > 6 THEN 2 
        ELSE 0 
        END
    , DATEADD(week, (@DaysToAdd / 5), @FromDate))

Note you can add or subtract days to go forwards and backwards in time, respectively.

Answer 7

Have you thought about pre-populating a look-up table that contains all of the working days (using your function) , for example WorkingDays(int DaySequenceId, Date WorkingDate), you can then use this table by selecting the DaySequenceId of the @fromDate and add @daysToAdd to get the new working date. Obviously this method also has the additional overhead of administering the WorkingDays table, but you could pre-populate it with the range of dates you expect. The other downside is the working dates that can be calculated will only be those contained within the WorkingDays table.

Answer 8

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

Answer 9

CREATE FUNCTION DateAddBusinessDays
(
    @Days int,
    @Date datetime  
)
RETURNS datetime
AS
BEGIN
    DECLARE @DayOfWeek int;

    SET @DayOfWeek = CASE 
                        WHEN @Days < 0 THEN (@@DateFirst + DATEPART(weekday, @Date) - 20) % 7
                        ELSE (@@DateFirst + DATEPART(weekday, @Date) - 2) % 7
                     END;

    IF @DayOfWeek = 6 SET @Days = @Days - 1
    ELSE IF @DayOfWeek = -6 SET @Days = @Days + 1;

    RETURN @Date + @Days + (@Days + @DayOfWeek) / 5 * 2;
END;

This function can add and subtract business days regardless of the value of @@DATEFIRST. To subtract business days use a negative number of days.

Answer 10

I found a much more elegant approach from Microsoft Docs. It takes into account skipping multiple weekends. Super clean.

CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
    WHILE @numDays>0
    BEGIN
       SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)

       SET @numDays=@numDays-1
    END

    RETURN CAST(@addDate AS DATETIME)
END
GO

Run the test

SELECT dbo.DAYSADDNOWK(GETDATE(), 15)

Answer 11

Thanks Damien for the code. There was a slight error in the calcs in that it added only 1 day for the sunday, and that when the number of business days crossed a weekend (but did not land in the weekend) the extra 2 days was not taken into account. Here is a modified version of Damiens code that works with the default datefirst at 7. Hope this helps.

CREATE FUNCTION [dbo].[fn_AddBusinessDays]  
(  
    @StartDate datetime,  
    @BusinessDays int  
) 
RETURNS datetime  
AS  
BEGIN 
DECLARE @EndDate datetime

SET @EndDate = DATEADD(day, @BusinessDays%5 + 
           CASE         
        WHEN DATEPART(weekday,@StartDate) +  @BusinessDays%5 > 6 THEN 2                  
        ELSE 0 
           END,     
   DATEADD(week,@BusinessDays/5,@StartDate))    

   RETURN @EndDate
END  
GO

Answer 12

The question's accepted answer produces incorrect results. E.g. select @fromDate = '03-11-1983', @DaysToAdd = 3 results in 03-14-1983 while 03-16-1983 is expected.

I posted a working solution here, but for completeness sake I will also add it here. If you are interested in the details of the two methods go visit my original answer. If not, simply copy/pasta this into your SQL project and use UTL_DateAddWorkingDays

Note that my solution only works if DATEFIRST is set to the default value of 7.

Test Script used to test various methods

CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays]
(   
    @date datetime,
    @days int
)
RETURNS TABLE AS RETURN 
(
    SELECT 
        CASE 
            WHEN @days = 0 THEN @date
            WHEN DATEPART(dw, @date) = 1 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 1, @date), @days - 1))
            WHEN DATEPART(dw, @date) = 7 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 2, @date), @days - 1))
            ELSE (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](@date, @days))
        END AS Date
)

CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays_Inner]
(   
    @date datetime,
    @days int
)
RETURNS TABLE AS RETURN 
(
    SELECT 
        DATEADD(d
        , (@days / 5) * 7 
          + (@days % 5) 
          + (CASE WHEN ((@days%5) + DATEPART(dw, @date)) IN (1,7,8,9,10) THEN 2 ELSE 0 END)
        , @date) AS Date
)

Answer 13

This is what I use:

SET DATEFIRST 1;

SELECT DATEADD(dw, (**NumberToAdd**/5)*7+(**NumberToAdd** % 5) + 
(CASE WHEN DATEPART(dw,**YourDate**) + (**NumberToAdd** % 5) > 5 
THEN 2 ELSE 0 END), **YourDate**) AS IncrementedDate
FROM YourTable t

The "SET DATEFIRST 1;" part is necessary to set Monday as the first day of the week.

Answer 14

WITH get_dates
AS
(
    SELECT getdate() AS date, 0 as DayNo 
    UNION ALL
    SELECT date + 1 AS date, case when DATEPART(DW, date + 1) IN (1,7) then DayNo else DayNo + 1 end
    FROM get_dates
    WHERE DayNo < 4
)
SELECT max(date) FROM get_dates
OPTION (MAXRECURSION 0) 

Answer 15

This is an old thread but I just created a table with all the dates then did this:

SELECT Count(*) 
FROM Date_Table
WHERE [day] BETWEEN @StartDate and @EndDate
    AND DATENAME(weekday, [day]) NOT IN ('Sunday', 'Saturday')

Answer 16

I don't have Sql Server at the moment to test but this is the idea:

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @dw integer
DECLARE @toDate datetime

set datefirst 1
set @toDate = dateadd(day, @daysToAdd, @fromDate)
set @dw = datepart(dw, @toDate)

if @dw > 5 set @toDate = dateadd(day, 8 - @dw, @toDate)

RETURN @toDate

END

Answer 17

I know it's a little bit late, perhaps someone else stumble upon this problem. I've tried the above solution but, most of them can't calculate holidays.

This is how i tried

CREATE function [dbo].[DateAddWorkDay]
(@days int,@FromDate Date)
returns Date
as
begin
declare @result date
set @result = (
select b
from
(
    SELECT
    b,
       (DATEDIFF(dd, a, b))
      -(DATEDIFF(wk, a, b) * 2)
      -(CASE WHEN DATENAME(dw, a) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, b) = 'Saturday' THEN 1 ELSE 0 END)
      -COUNT(o.Holiday_Date) 
      as workday
    from
    (
    select 
    @FromDate as a,
    dateadd(DAY,num +@days,@FromDate) as b
    from (select row_number() over (order by (select NULL)) as num
          from Information_Schema.columns
         ) t
    where num <= 100 
    ) dt
    left join Holiday o on o.Holiday_Date between a and b and DATENAME(dw, o.Holiday_Date) not in('Saturday','Sunday') 
    where DATENAME(dw, b) not in('Saturday','Sunday')
          and b not in (select Holiday_Date from OP_Holiday where Holiday_Date between a and b) 

    group by a,b
) du
where workday =@days 


)
return @result 
end

Where Holiday is a table with holiday_date as a reference for holiday

Hope this can help some one.

Answer 18

This SQL function works similar to Excel WORKDAY function. Hope it can help you.

CREATE FUNCTION [dbo].[BusDaysDateAdd] 
(
   @FromDate date,
   @DaysToAdd int
)
RETURNS date
AS
BEGIN
   DECLARE @Result date
   DECLARE @TempDate date
   DECLARE @Remainder int
   DECLARE @datePartValue int

   SET @TempDate = (DATEADD(week, (@DaysToAdd / 5), @FromDate))
   SET @Remainder = (@DaysToAdd % 5)
   SET @datePartValue = DATEPART(weekday, @TempDate)
   SET @Result = DATEADD(day,@Remainder + CASE WHEN @Remainder > 0 AND @datePartValue = 7 THEN 1
                                                WHEN @Remainder >= 1 AND @datePartValue = 6 THEN 2
                                                WHEN @Remainder >= 2 AND @datePartValue = 5 THEN 2
                                                WHEN @Remainder >= 3 AND @datePartValue = 4 THEN 2
                                                WHEN @Remainder >= 4 AND @datePartValue = 3 THEN 2
                                                WHEN @Remainder >= 5 AND @datePartValue = 2 THEN 2
                                                ELSE 0 END, @TempDate)
   RETURN @Result
END
GO

Reference

Answer 19

For Germany all of the answers don't work.

The only function I tested and works is a translation from an old Excel form here:

Set @EndDate=Dateadd(DAY,@DaysToAdd,@FromDate) +
Cast(((
         CASE WHEN 5 <= DATEPART(weekday, @FromDate)%7 
        THEN 5
         ELSE 
         DATEPART(weekday, @FromDate)%7
         END)
      -1 + @DaysToAdd )/5 
 as int) 
* 2 - 
   (Case when DAtepart(weekday, @FromDate)=6 then 1 else 0 end) 

Answer 20

I'm a little late to this party but I wound up writing my own version of this, because of drawbacks in the other solutions. Specifically this version addresses counting backwards, and starting on weekends.

There's an ambiguous situation that could arise, if you add zero business days to a weekend date. I've kept the date the same, but you can leave out this check if you always want to force a weekday to be returned.

CREATE FUNCTION [dbo].[fn_AddBusinessDays]
(
    @date datetime,
    @businessDays int
)
RETURNS datetime
AS
BEGIN
    --adjust for weeks first
    declare @weeksToAdd int = @businessDays / 7
    declare @daysToAdd int = @businessDays % 7

    --if subtracting days, subtract a week then offset
    if @businessDays < 0 begin
        set @daysToAdd = @businessDays + 5
        set @weeksToAdd = @weeksToAdd - 1
    end

    --saturday becomes zero using the modulo operator
    declare @originalDayOfWeek int = datepart(dw, @date) % 7
    declare @newDayOfWeek int = datepart(dw, dateadd(d, @daysToAdd, @date)) % 7

    --special case for when beginning date is weekend
    --adding zero on a weekend keeps the same date. you can remove the <> 0 check if you want Sunday + 0 => Monday
    declare @dateOffset int = case
        when @businessDays <> 0 and @originalDayOfWeek = 0 then 2
        when @businessDays <> 0 and @originalDayOfWeek = 1 then 1
        when @businessDays <> 0 and @newDayOfWeek < @originalDayOfWeek then 2
        else 0
    end

    -- Return the result of the function
    return dateadd(d, @daysToAdd + @dateOffset, dateadd(ww, @weeksToAdd, @date))

END

Answer 21

I've very recently solved this problem to add two working days to the current date by creating an INT value @DaysToAdd - tested and working great on 2008 / 2012.

DECLARE @DaysToAdd INT

SELECT @DaysToAdd = CASE  
   WHEN DATEPART(WEEKDAY,GETDATE()) =  1 THEN 3 -- Sunday -> Wednesday
   WHEN DATEPART(WEEKDAY,GETDATE()) =  5 THEN 4 -- Thursday -> Monday
   WHEN DATEPART(WEEKDAY,GETDATE()) =  6 THEN 4 -- Friday -> Tuesday
   WHEN DATEPART(WEEKDAY,GETDATE()) =  7 THEN 4 -- Saturday -> Wednesday
   ELSE 2 END

SELECT DATEADD(DAY, @DaysToAdd, GETDATE()) AS TwoWorkingDaysTime

Answer 22

I just tested the accepted answer and found that it does not work when Sunday is the start day.

You need to add the following under the Select @Saturday line item:

SELECT @fromDate = CASE WHEN DATEPART(weekday,@fromDate) = 1 THEN DATEADD(day,1,@fromDate) ELSE @fromDate END

Answer 23

Sigh. I can't believe after all these decades there's still no : a) standard "DateAddWorkDays" in Microsoft SQL Server (even though Microsoft has had a WorkDay Function in Excel forever) and b) clear solution in here or anywhere else I can find that handles all issues people have raised.

Here's a solution I developed that addresses the following issues that seemingly all the above answers here and elsewhere I've been able to find has one or more of. This handles:

1. Mnemonic identifier names.
2. Comments explaining code that's not clear.
3. Not checking every single work day needing to be incremented (i.e. much less than O(n) complexity).
4. Negative work day increments.
5. Allowing non-12 am time portion to be passed in (so you won't have to strip it first).
6. Retaining the passed-in time portion, if any, in the result (in case you need the exact time x-business days ahead/ago).
7. Weekend day names in languages other than English.
8. @@DateFirst values other than the default (7 aka U.S.).
9. Specifying a custom list of non-weekend non-working days.
10. Allowing list of non-weekend non-working days to work if passed-in date has a non-12 am time.
11. Returning starting date-time if # work days increment is 0 even if starting date-time is on a non-working day.
12. Moving to the next / previous working day first before starting to increment / decrement working days, respectively. NOTE: This differs from Excel's WorkDay Function, but I believe this is more useful and intuitive. Ex. If you get an inquiry / order on a weekend day, and you have an SLA (i.e. response time, delivery date) of 1 business day, you shouldn't have to respond / deliver until 1 full working day has passed (regardless of how many adjacent non-working days preceeded it).
13. Skipping any additional weekends and/or non-working weekdays that may have been spanned after adding any non-working weekdays back in that may have been spanned when adding initial weekends spanned when adding # of working days alone - and repeating until no longer necessary.

SUGGESTIONS: Of course, as with any recursive algorithm, this one can be converted to an iterative one (by implementing your own stack, i.e. with a Temp Table), but I think the 32 nesting levels is way more than enough for the vast majority of real-world use cases. Also, of course, you can make it more generic / portable by passing in the non-working weekday dates as a Table-Valued Parameter vs. a hard-coded Table reference.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================================================================================================
-- Author:      Tom
-- Create date: 03/13/2017
-- Description: Add specified # of working days (+/-) to a specified date-time assuming existence of a list of non-work weekday 
--  dates (incl. holidays, weather days, utility outage days, fire days, etc.) in the 'NonWorkDayDate' Column of a 'NonWorkWeekday' 
--  Table. If specified # working days is 0, the specified date-time is returned.  Working days are not added until the specified 
--  date-time has first been incremented (+/-) to the next working day in the direction of the working days increment.
--  NOTE: Uses a forumla (vs. O(n) loop) that uses recusion whenever days incremented (incl. weekends) spans non-work weekdays.
--  !!!WARNING!!!: Will exceed SQL Server nesting level (32) if abs (# of working days) < ~1 / 32 adjacent non-working days.
-- Parameters:
--  @RefDateTime    DateTime:   Reference date-time to which to add '@WorkDaysIncrement'.
--  @WorkDaysIncrement  Int:    # of working days (+/-) to add # to the '@RefDateTime'.
-- Returns:
--  1. Result of @RefDateTime + @WorkDaysIncrement (skipping weekend and holiday dates and retaining the @RefDateTime's time).
-- ===================================================================================================================================
CREATE FUNCTION [dbo].[AddWorkDays_Recursive] 
(
    -- Add the parameters for the function here
    @RefDateTime datetime,
    @WorkDaysIncrement int
)
RETURNS DateTime
AS
BEGIN

-- If no days to increment, return passed in date-time (even if weekend day).
    if (@WorkDaysIncrement = 0) return @RefDateTime

-- Set the one-day increment used to add or subtract one calendar/work day.
    declare @OneDayIncrement int = sign(@WorkDaysIncrement)

-- Initialize # of calendar days added to 0.
    declare @DaysAdded int = 0

-- Set reference date to date (i.e. excl. time) of reference date-time.
    declare @RefDate datetime = convert
        (
            date,
            convert
            (
                varchar(10),
                @RefDateTime,
                101
            )
        ) 
    --end declare @RefDate 

-- Initialize result date to reference date
    declare @ResultDate datetime = @RefDate

-- Set U.S. Weekday # to the 1-based U.S. weekday # result date.
    declare @USWeekdayNumber tinyint = ((datepart(weekday, @ResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7

-- If result date is now on a weekend day, set #  of weekend days increment so that we can move it +/- 1 to 2 days to next weekday.
    declare @WeekendDaysInc smallint = 
    (
        case (@USWeekdayNumber)

            when 1 then --Sunday 
                case
                    when (@OneDayIncrement > 0) then 1
                    else -2 
                end 
            --end when 1 --Sunday

            when 7 then --Saturday 
                case
                    when (@OneDayIncrement > 0) then 2
                    else -1
                end 
            --end when 7 then --Saturday 

            else 0 -- Not Weekend Day #

        end -- case (@USWeekdayNumber)
    ) -- end declare @WeekendDaysInc smallint = 

-- Increment # of calendar days added by #  of weekend days increment
    set @DaysAdded += @WeekendDaysInc

-- Increment result date by #  of weekend days increment
    set @ResultDate += @WeekendDaysInc 

-- Set # of work weeks increment to # of full 5-day increments in the # (+/-) of work days to increment.
    declare @WorkWeeksIncrement int = @WorkDaysIncrement / 5

-- Increment # of calendar days added by 7 times # of work weeks increment, i.e. to add weekday + weekend days for full weeks.
    set @DaysAdded += @WorkWeeksIncrement * 7

-- Set result date after full weeks added to reference date + # of calendar days 
    declare @AfterFullWeeksResultDate datetime = @ResultDate + @DaysAdded

-- Set # partial-work week days to # (+/-) of work days to increment left after adding full weeks.
    declare @PartialWorkWeekDays int = @WorkDaysIncrement % 5

-- Increment # of calendar days added by # partial-work week days
    set @DaysAdded += @PartialWorkWeekDays

-- Set result date after partial week added to result date after full weeks added + # partial work week days
    declare @AfterPartialWeekResultDate datetime = @AfterFullWeeksResultDate + @PartialWorkWeekDays

--Set result date to result date after partial week.
    set  @ResultDate = @AfterPartialWeekResultDate

-- Set After Full Weeks U.S. Weekday # to the 1-based U.S. weekday # result date.
    declare @AfterFullWeeksUSWeekdayNumber tinyint = 
        (
            ((datepart(weekday, @AfterFullWeeksResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
        )

-- Set After Partial Week U.S. Weekday # to the 1-based U.S. weekday # result date.
    declare @AfterPartialWeekUSWeekdayNumber tinyint = 
        (
            ((datepart(weekday, @AfterPartialWeekResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
        )

--If (incrementing and After Full Weeks U.S. Weekday # > @AfterPartialWeekUSWeekdayNumber) 
--  or (decrementing and After Full Weeks U.S. Weekday # < @AfterPartialWeekUSWeekdayNumber), increment by (+/-) 2 to account for 
--  the weekend that was spanned when partial-work week days were added.
    if 
    (
        (
            (@OneDayIncrement > 0)
            and (@AfterFullWeeksUSWeekdayNumber > @AfterPartialWeekUSWeekdayNumber)
        )
        or (
            (@OneDayIncrement < 0)
            and (@AfterFullWeeksUSWeekdayNumber < @AfterPartialWeekUSWeekdayNumber)
        )

    )
    begin
        set @WeekendDaysInc = 2 * @OneDayIncrement
        set @DaysAdded += @WeekendDaysInc
        set @ResultDate += @WeekendDaysInc
    end -- if need to increment to account for weekend spanned by partial-work week days,

-- Set U.S. Weekday # to the 1-based U.S. weekday # result date.
    set @USWeekdayNumber = ((datepart(weekday, @ResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7

-- If result date is now on a weekend day, set #  of weekend days increment so that we can move it +/- 1 to 2 days to next weekday.
    set @WeekendDaysInc = 
    (
        case (@USWeekdayNumber)

            when 1 then --Sunday 
                case
                    when (@OneDayIncrement > 0) then 1
                    else -2 
                end 
            --end when 1 --Sunday

            when 7 then --Saturday 
                case
                    when (@OneDayIncrement > 0) then 2
                    else -1
                end 
            --end when 7 then --Saturday 

            else 0 -- Not Weekend Day #

        end -- case (@USWeekdayNumber)
    ) -- end declare @WeekendDaysInc smallint = 

-- Increment # of calendar days added by #  of weekend days increment
    set @DaysAdded += @WeekendDaysInc

-- Increment result date by #  of weekend days increment
    set @ResultDate += @WeekendDaysInc 

-- Set non-work weedays count to # Rows where NonWorkDayDate between RefDate and ResultDate (if # of work days to increment > 0), else between 
--  ResultDate and RefDate.
    declare @NonWorkWeekdaysCount int =
    (
        select count(nw.NonWorkDayDate) 
            from NonWorkWeekday as nw
            where 
                (
                    (@OneDayIncrement > 0)
                    and (nw.NonWorkDayDate between @RefDate and @ResultDate)
                )
                or (
                    (@OneDayIncrement < 0)
                    and (nw.NonWorkDayDate between @ResultDate and @RefDate)
                )
        --end select count(nw.NonWorkDayDate) from Holidate as nw 
    ) -- end declare @HolidaysSpanned int =

-- Set result date-time to reference date-time + # of calendar days added
    declare @ResultDateTime datetime = @RefDateTime + @DaysAdded 

-- Set result date-time equal to result of adding (# of holidays x one-day increment).
    set @ResultDateTime = dbo.AddWorkDays_Recursive
        (
            @ResultDateTime, -- @RefDateTime
            @NonWorkWeekdaysCount * @OneDayIncrement -- @WorkDaysIncrement
        )
    --end set @ResultDateTime = 

    -- Return the result of the function
    RETURN @ResultDateTime

END

GO

Answer 24

--Refactoring my original answer... I've added the option to define the starting point of the calculation if the starting date happens to be a weekend day: start from that weekend day or shift to the nearest weekday depending on the direction of the delta.

DECLARE
    @input DATE = '2019-06-15', -- if null, then returns null
    @delta INT = 1, -- can be positive or negative; null => zero
    @startFromWeekend BIT = 1 -- null => zero

-- input is null, delta is zero/null
IF @input IS NULL OR ISNULL(@delta, 0) = 0
    SELECT @input

-- input is not null and has delta
ELSE
BEGIN
    DECLARE
        @input_dw INT = (DATEPART(DW, @input) + @@DATEFIRST - 1) % 7, -- input day of week
        @weeks    INT = @delta / 5, -- adjust by weeks
        @days     INT = @delta % 5  -- adjust by days

    -- if input is a weekend day, offset it for proper calculation
    -- !!important!!: depends on *your* definition of the starting date to perform calculation from
    DECLARE @offset INT =
        -- start calc from weekend day that is nearest to a weekday depending on delta direction
        -- pos delta: effectively Sunday of the weekend   (actual: prev Friday)
        -- neg delta: effectively Saturday of the weekend (actual: next Monday)
        CASE WHEN ISNULL(@startFromWeekend, 0) = 1
        THEN CASE WHEN @delta > 0
            THEN CASE @input_dw
                WHEN 0 THEN -2
                WHEN 6 THEN -1
                END
            ELSE CASE @input_dw
                WHEN 0 THEN  1
                WHEN 6 THEN  2
                END
            END
        -- start calc from nearest weekday depending on delta direction
        -- pos delta: next Monday from the weekend
        -- neg delta: prev Friday from the weekend
        ELSE CASE WHEN @delta > 0
            THEN CASE @input_dw
                WHEN 0 THEN  1
                WHEN 6 THEN  2
                END
            ELSE CASE @input_dw
                WHEN 0 THEN -2
                WHEN 6 THEN -1
                END
            END
        END

    -- calculate: add weeks, add days, add initial correction offset
    DECLARE @output DATE = DATEADD(DAY, @days + ISNULL(@offset, 0), DATEADD(WEEK, @weeks, @input))

    -- finally, if output is weekend, add final correction offset depending on delta direction
    SELECT
        CASE WHEN (DATEPART(DW, @output) + @@DATEFIRST - 1) % 7 IN (0,6)
        THEN CASE 
            WHEN @delta > 0 THEN DATEADD(DAY,  2, @output)
            WHEN @delta < 0 THEN DATEADD(DAY, -2, @output)
            END
        ELSE @output
        END
END

Answer 25

I could not find a satisfactory solution to this that I could understand, so I ended up mostly writing one myself. This started off structurally similar to Damien_The_Unbeliever's answer but diverged quite a bit as I couldn't get it to work.

My requirements

• I'm using Redshift, so has to work there.
• Negative number-of-days inputs must work (e.g. add -1 or -12 business days).
• The output must be correct when input date is a weekday (e.g. Mon + 2 → Wed; Tue - 4 → previous Wed).
• The solution must be documented.

Nice-to-haves

• Sane output for weekend days. (I chose to roll weekend days to their following Mondays, so e.g. Sunday + 1 == Monday + 1 == Tuesday.)

Solution

Note: My company uses Periscope Data for BI which has a C-macro-like syntax sugar to define inline text replacements it calls Snippets (see docs). Should be easily translatable to pure SQL though -- feel free to suggest an edit to my answer if you've done that translation.

Snippet: add_business_days(date,num_days)

(dateadd(
  day
  , (
    7 * (([num_days]) / 5)                                    -- add whole weeks
    + (([num_days]) % 5)                                      -- add remaining days after taking out whole weeks
    + case when (                                             -- if (
        extract(dow from [roll_forward_to_weekday("[date]")]) --   day of week of "rolled forward" date (i.e. weekends → Monday)
         + (([num_days]) % 5)                                 --   + remaining days after taking out whole weeks
        not between 1 and 5                                   --   is NOT a weekday of the same week
      )                                                       -- )
        then sign([num_days])::int * 2                        -- then increase magnitude of num_days by 2 to jump over the weekend
        else 0
      end
  )                                                           -- start from the "rolled forward" date because adding business days to ..
  , [roll_forward_to_weekday("[date]")]                       -- Saturday or Sunday is equivalent to adding them to the following Monday.
                                                              -- (note: due to ^, add_business_days(Saturday or Sunday,0) == Monday)
))

Snippet: roll_forward_to_weekday(date)

(dateadd(
  day
  , case extract(dayofweek from([date]))
      when 6 /* Saturday */ then 2
      when 0 /* Sunday   */ then 1
                            else 0
    end
  , ([date])
))

Answer 26

Very late to the party, but I stumbled upon the very same question. And though there are a myriad of answers here, I just wanted to add my solution, b/c the solutions here are:

1. Either not working with negative offsets
2. Or do not work with different @@DATEFIRST settings
3. Or are using loops for something which could be solved solely with modulus arithmetic.
4. or are overly complicated in their branching logic

Thus, here's my solution, which I validated using Excel's WORKDAY function for positive and negative offsets:

CREATE OR ALTER FUNCTION dbo.AddBusinessDays(@startdate AS DATETIME, @n AS INT)
   RETURNS DATETIME
AS
BEGIN
   DECLARE @result DATETIME;
   DECLARE @nrweeks INT,
           @nrdays INT;
   DECLARE @wd TINYINT;
   DECLARE @residdays SMALLINT;
   SET @wd = ((DATEPART(DW, @startdate) - 1) + @@DATEFIRST) %  7;
   --- 6 working days correspond to 1 full week and 1 extra day
   SET @nrweeks = @n / 5;
   SET @residdays = @n % 5;
   /*
   (1) transform working weeks into calendar weeks
   (2) if residual days + starting day touches a saturday add 2 days for the weekend
   (3) unless if we started on a saturday we should not count it, e.g. SAT + 1 WD would result in 1 + 2 => TUE, but it should be MON so 1 + 2 - 1
   (4) if we have a full working week w/o residual days and started on the weekend remove touching condition altogether, e.g. SAT + 5 WD: 7 + 2 - 1 => SUN, but it should be FRI, so 7 + 2 - 1 - 2 
   (1a) - (4a) likewise but for negative logic, i.e. all logic regarding SAT should be logic regarding SUN and signs have to be switched
   */
   IF @n = 0
      SET @nrdays = 0;
   ELSE IF @n > 0
      SET @nrdays = @residdays + 7 * @nrweeks +                    --- (1)
                       IIF(@wd + @residdays >= 6, 2, 0) +          --- (2)
                       IIF(@wd = 6, -1, 0) +                       --- (3)
                       IIF(@residdays = 0 AND @wd % 6 = 0, -2, 0); --- (4)
   ELSE
      SET @nrdays = @residdays + 7 * @nrweeks +                    --- (1a)
                       IIF(@wd + @residdays <= 0, -2, 0) +         --- (2a)
                       IIF(@wd = 0, 1, 0) +                        --- (3a)
                       IIF(@residdays = 0 AND @wd % 6 = 0, 2, 0);  --- (4a)   
   SET @result = DATEADD(DAY, @nrdays, @startdate);
   RETURN @result;
END

N.B. This solution is obviously made for T-SQL.