How do I add to some date excluding weekends using SQL Server?

Question

For example:

@dtBegin = '2012-06-29' 

@input = 20

I want the output to be '2012-07-27'.

Solution

I had to tackle the same problem in my project. Gordon Lionoff's solution got me on the right track but did not always produce the right result. I also have to take in account dates that start in a weekend. I.e. adding 1 business day to a saturday or sunday should result in a monday. This is the most common approach to handling business day calculation.

I've created my own solution based on Gordon Linoff's function and Patrick McDonald's excellent C# equivalent

NOTE: My solution only works if DATEFIRST is set to the default value of 7. If you use a different DATEFIRST value, you will have to change the 1, 7 and (1,7,8,9,10) bits.

My solution consists of two functions. An "outer" function that handles edge cases and an "inner" function that performs the actual calculation. Both functions are table-valued functions so they will actually be expanded into the implementing query and fed through the query optimizer.

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
)

As you can see, the "outer" function handles:

• When adding no days, return the original date. This will keep saturday and sunday dates intact.
• When adding days to a sunday, start counting from monday. This consumes 1 day.
• When adding days to a saturday, start counting from monday. This consumes 1 day.
• In all other cases, perform the usual calculation

_

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
)

The "inner" function is similar to Gordon Linoff's solution, except it accounts for dates crossing weekend boundaries but without crossing a full week boundary.

Finally, I created a test script to test my function. The expected values were generated using Patrick McDonald's excellent C# equivalent and I randomly cross-referenced this data with this popular calculator.

Comments

[Curtis]

possible duplicate of Add business days to date in SQL without loops

[vergenzt]

See this question. Edit: I'm pretty sure that question answers this one exactly.

[Hiten004]

Have you seen this ? Using Dateadd() to ignore weekends : MSDN Article

[Clockwork-Muse]

Possible candidate for a UDF, then; addWeekdays or somesuch.

[Martin Devillers]

This solution produces incorrect results: I.e. Adding 3 days to thursday 20 september 2012 should produce wednesday 26 september 2012. However, this function produces monday 24 september 2012.

[shawnt00]

Untested, but this might work in place of the case expression if you like obfuscation! abs(ceiling(@input%5 + datepart(dw, @dtbegin)%7 - 3) / 3 * 3 / 2.0))

[shawnt00]

Performance with functions can be slow so watch out since you've got those nested calls in there. Do you really need a table function?

[Zohar Peled]

I wouldn't attempt datetime arithmetics. It's too easy to mess it up