0

I need to add five days to the "2020-12-01" date. But here the scenario is while adding five days to "2020-12-01" date we should not include the Saturday (2020-12-05) and Sunday (2020-12-06).

I have tried with DATEDIFF with filter options in where conditions.. but I am unable to get the solution. Can any one please help me to get it done. (Expected Output : 2020-12-07)

GMB
  • 216,147
  • 25
  • 84
  • 135
venugopal
  • 79
  • 10

1 Answers1

1

It might be simpler with a brute force approach, by enumerating the days in a subquery, decrementing the coutner for non-weekend days only:

declare @dt date = '20201202';
declare @no_days int = 5;

with cte as (
    select @dt as dt, @no_days as no_days
    union all
    select dateadd(day, 1, dt), 
        case when datename(weekday, dt) in ('Saturday', 'Sunday') 
            then no_days
            else no_days - 1
        end
    from cte
    where no_days > 1
)
select max(dt) as res from cte

Demo on DB Fiddle (SQL Server 2016):

| res        |
| :--------- |
| 2020-12-11 |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Tip: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. – HABO Dec 29 '20 at 14:59