Add days to Date excluding Weekends in SQL Server

Last Reply 2 months ago By Waghmare

Posted 2 months ago

Hi Team,

I have a Table which contains a DateField. Where i want to show the datefield + 25 business days (excluding Sat n Sun) date as output.

If date date 02nd Aug then the output should be the 2nd Aug plus 25 business days date.

Posted 2 months ago

I have tried below sql code. Please check.

declare @d1 datetime, @d2 datetime
declare @daydiff int
declare @CalendarDate datetime
					
select @d1 =  '08/04/2019'

select @CalendarDate = DATEADD(day,25,@d1)  

set @d2 = DATEADD(Day,35,@d1) -- 25days - 35 , 27days - 38,

--select @d2  as BusinessDate

select @daydiff = datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -  --- this returns skipped week off days
			case when datepart(dw, @d1) = 1 then 1 else 0 end +
			case when datepart(dw, @d2) = 1 then 1 else 0 end 

--select @daydiff as WorkingDays
--condition

if(DATEPART(dw,@d2)=1)
BEGIN
set @d2=DATEADD(day,1,@d2)
END

else if (DATEPART(dw,@d2)=7)
BEGIN
set @d2=DATEADD(day,2,@d2)
END							

select @d2 as Businessdate,@CalendarDate as CalendarDate