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

Last Reply 2 months ago By dharmendr

Posted 2 months 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
You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago Modified on 2 months 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