Add Days to Date excluding Weekends (Saturday and Sunday) using While loop in SQL Server

Last Reply 15 days ago By dharmendr

Posted 15 days ago

Hi Team,

As per the below logic i am unable to get the correct business date.

Please check the below login and help.

Date  CalendarDate 27day BusinessDate 27day daydiff27
7/16/2019 8/12/2019 8/22/2019 27
7/19/2019 8/15/2019 8/26/2019 26
7/20/2019 8/16/2019 8/26/2019 25

Below logic not works in below case.

Date : '07/20/2019'

Output Business Date : 2019-08-26

Correct Output Date : 2019-08-27

Date : '07/16/2019'

Output Business Date : 2019-08-20

Correct Output Date : 2019-08-22

Have checked the output on below link.

https://www.timeanddate.com/date/weekdayadd.html?d1=16&m1=7&y1=2019&

declare @d1 datetime, @d2 datetime
declare @daydiff27 int
declare @CalendarDate_27day datetime
declare @BusinessDate_27day datetime
 
select @d1 =  '08/04/2019'
 
set @CalendarDate_27day = DATEADD(day,27,@d1)  

set @d2 = DATEADD(Day,37,@d1) -- 27days - 37 					

select @daydiff27 = 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 
--condition

if(@daydiff27 =25)
begin
	set @d2 = DATEADD(Day,38,@d1) -- 27days - 37 					

	select @daydiff27 = 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 
end

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							

set @BusinessDate_27day = @d2                      
 
select @d2 as Businessdate,@CalendarDate as CalendarDate
Posted 15 days ago Modified on 13 days ago

Hi Waghmare,

Check with the below query.

SQL

DECLARE @d1 DATETIME
DECLARE @BusinessDate_27day DATETIME
  
SET @d1 =  '08/04/2019'
DECLARE @count INT = 0
WHILE @count < 27
Begin
    SET @count = @count + 1
    SET @BusinessDate_27day = DATEADD(DAY,1,@d1)
    SET @d1 = @BusinessDate_27day
    IF DATEPART(WEEKDAY,@d1) =  1 OR DATEPART(WEEKDAY,@d1) =  7
    BEGIN
        SET @count = @count - 1
    END
END
  
SELECT @BusinessDate_27day AS Businessdate

Output

10.09.2019