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
