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

possible duplicate of [Add business days to date in SQL without loops](http://stackoverflow.com/questions/5471524/add-business-days-to-date-in-sql-without-loops) — Curtis, Jul 11 '12 at 15:03
See [this question](http://stackoverflow.com/questions/5471524/add-business-days-to-date-in-sql-without-loops). **Edit:** I'm pretty sure that question answers this one exactly. — vergenzt, Jul 11 '12 at 15:04
Have you seen this ? [Using Dateadd() to ignore weekends](http://social.msdn.microsoft.com/Forums/zh/transactsql/thread/63e15c8c-8dc1-4fff-8666-b850c7216db6) : **MSDN Article** — Hiten004, Jul 11 '12 at 15:12

score 1 · Answer 1 · edited May 23 '17 at 12:28

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.

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? — shawnt00, Mar 16 '18 at 14:34

score 0 · Answer 2 · answered Jul 11 '12 at 15:28

0

You can do this without resorting to a calendar table or user defined function:

dateadd(d,
        (@input / 5) * 7 + -- get complete weeks out of the way
        mod(@input, 5) + -- extra days
        (case when ((@input%5) + datepart(dw, @dtbegin)%7) in (7, 1, 8) or
                   ((@input%5) + datepart(dw, @dtbegin)%7) < (@input%5)
              then 2
              else 0
         end),
        @dtbegin
       )

I'm not saying this is pretty. But sometimes arithmetic is preferable to a join or a loop.

answered Jul 11 '12 at 15:28

Gordon Linoff

1,242,037
58
646
786

Possible candidate for a UDF, then; `addWeekdays` or somesuch. – Clockwork-Muse Jul 11 '12 at 15:33
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. – Martin Devillers Sep 20 '12 at 15:00
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 Mar 16 '18 at 10:49
I wouldn't attempt datetime arithmetics. It's too easy to mess it up – Zohar Peled Oct 08 '20 at 04:21

score 0 · Answer 3 · edited Mar 16 '18 at 10:11

This is what I've 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.

score 0 · Answer 4 · answered Jan 24 '21 at 16:43

I realize I'm about 8 years late to this party... But I took Martin's answer and updated it to:

1. a single scalar function instead of 2 nested table-valued functions

I tested using his original test script and my function passes too. Also, it seems the rebuild to a scalar function has a slight positive impact on performance. Both versions seem to benefit equally from 'buffer caching', the scalar version performing up to 25% better non-cached and up to 40% better cached. Disclaimer: I just ran both versions a bunch of times and recorded the times, I did not do any decent performance testing.

2. include support for DATEFIRST is either Monday, Saturday or Sunday

I feel a UDF should be agnostic to the datefirst setting. I'm in Europe and Monday is the default here. The original function would not work without adaptation.

According to wikipedia Monday, Saturday and Sunday are the only real world first days of the week. Support for other could easily be added, but would make the code more bulky and I have a hard time envisioning a real world use case.

CREATE FUNCTION dbo.fn_addWorkDays
(   
    @date datetime,
    @days int
)
RETURNS DATETIME 
AS
BEGIN
    
    IF NOT @@DATEFIRST IN (1,6,7) BEGIN --UNSUPPORTED DATE FIRST
        RETURN NULL
    /* MONDAY = FRIST DAY */
    END ELSE IF @days = 0 BEGIN 
        RETURN @date
    END ELSE IF @@DATEFIRST = 1 AND DATEPART(dw, @date) = 7 BEGIN --SUNDAY
        SET @date = DATEADD(d, 1, @date)
        SET @days = @days - 1
    END ELSE IF @@DATEFIRST = 1 AND DATEPART(dw, @date) = 6 BEGIN --SATURDAY
        SET @date = DATEADD(d, 2, @date)
        SET @days = @days - 1

    /* SATURDAY = FRIST DAY */
    END ELSE IF @@DATEFIRST = 7 AND DATEPART(dw, @date) = 2 BEGIN --SUNDAY
        SET @date = DATEADD(d, 1, @date)
        SET @days = @days - 1
    END ELSE IF @@DATEFIRST = 7 AND DATEPART(dw, @date) = 1 BEGIN --SATURDAY
        SET @date = DATEADD(d, 2, @date)
        SET @days = @days - 1

    /* SUNDAY = FRIST DAY */
    END ELSE IF @@DATEFIRST = 7 AND DATEPART(dw, @date) = 1 BEGIN --SUNDAY
        SET @date = DATEADD(d, 1, @date)
        SET @days = @days - 1
    END ELSE IF @@DATEFIRST = 7 AND DATEPART(dw, @date) = 7 BEGIN --SATURDAY
        SET @date = DATEADD(d, 2, @date)
        SET @days = @days - 1

    END 

    DECLARE @return AS dateTime
    
    SELECT @return  =
        DATEADD(d
        , (@days / 5) * 7
          + (@days % 5)
          + (CASE 
                /* MONDAY = FRIST DAY */
                WHEN @@DATEFIRST = 1 AND ((@days%5) + DATEPART(dw, @date)) IN (6,7,8,9) THEN 2 
                /* SATURDAY = FRIST DAY */
                WHEN @@DATEFIRST = 7 AND ((@days%5) + DATEPART(dw, @date)) IN (1,2,8,9,10) THEN 2 
                /* SUNDAY = FRIST DAY */
                WHEN @@DATEFIRST = 7 AND ((@days%5) + DATEPART(dw, @date)) IN (1,7,8,9,10,11) THEN 2 
                ELSE 0 
            END)
        , @date) 

    RETURN @return 
END

I hope this might benefit someone!

score 0 · Answer 5 · edited Sep 16 '21 at 12:08

you can use the below mentioned code for exclude weekends

go
if object_id('UTL_DateAddWorkingDays') is not null
drop function UTL_DateAddWorkingDays
go
create FUNCTION [dbo].[UTL_DateAddWorkingDays]
(   
    @date datetime,
    @daysToAdd int
)
RETURNS date
as
begin
declare @daysToAddCnt int=0,
@Dt datetime
declare @OutTable table
(
    id int identity(1,1),
    WeekDate date,
    DayId int
)
while @daysToAdd>0
begin
set @Dt=dateadd(day,@daysToAddCnt,@date)
--select @daysToAddCnt cnt,@Dt date,DATEPART(weekday,@Dt) dayId,@daysToAdd daystoAdd
    if(DATEPART(weekday,@Dt) <>7 and DATEPART(weekday,@Dt)<>1)
    begin
    insert into @outTable (WeekDate,DayId)
    select @Dt,DATEPART(weekday,DATEADD(day,@daysToAddCnt,@Dt))
    set @daysToAdd=@daysToAdd-1
    end
    set @daysToAddCnt=@daysToAddCnt+1

end
select @Dt=max(WeekDate) from @outTable
return @Dt
end

score 0 · Answer 6 · answered Nov 09 '22 at 09:58

what about this?

declare @d1 date='2012-06-29'
declare @days int=20

select dateadd(dd,@days,@d1)

select dateadd(dd,case DATEPART(dw,t1.d) when 6 then +2 when 7 then +1 else +0 end,t1.d)
from
(
    select dateadd(dd,CEILING((convert(float,@days)/5)*2)+@days,@d1)d
)t1

i found how many we in the range by CEILING((convert(float,@days)/5)*2) then i added them to date and at the end i check if is a saturday or sunday and i add 1 or 2 days.

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

6 Answers6

Linked