This is best done with a "calendar table".
See "udf_AddBusinessDays":
https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi
How to get two business days from today(getdate()) exclude weekend and holidays?
Holiday table:
Create Table Holiday_Table( HDate date)
Insert INTO Holiday_Table(HDate)
values ('2021-12-31')
,('2021-12-28')
,('2021-12-27')
,('2021-12-24')
,('2021-11-26')
,('2021-11-25')
,('2021-09-06')
if getdate()='2021-09-07' then result should be '2021-09-02'
if getdate()='2021-11-29' then result should be '2021-11-23'
if getdate()='2021-11-30' then result should be '2021-11-24'
if getdate()='2021-12-29' then result should be '2021-12-22'
This is best done with a "calendar table".
See "udf_AddBusinessDays":
https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/
Please also try:
DECLARE @workday INT
,@dt_begin DATE
,@today DATE
SET @today =GETDATE()
SET @workday =0
SET @dt_begin = DATEADD(d,-1,@today)
WHILE @workday < 2
BEGIN
SELECT @workday = CASE WHEN DATEPART(dw, @dt_begin) BETWEEN 1 AND 5
AND @dt_begin NOT IN(SELECT HDate FROM Holiday_Table)
THEN @workday + 1 ELSE @workday END
SET @dt_begin =DATEADD(d,-1,@dt_begin)
IF @workday=2
SELECT @today,DATEADD(d,1,@dt_begin)
END
Regards,
Echo
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
One way
Declare @isi Table(TheDate date);
With cte As
(Select GetDate() As TheDate
Union All
Select DateAdd(day, -1, TheDate) As TheDate From cte
Where DateDiff(day, TheDate, GetDate())< 30)
Insert @Days(TheDate) Select TheDate From cte;
Delete From d
From @Days d
Where d.TheDate In (Select h.HDate From Holiday_Table h)
Or DateDiff(day, '19000101', d.TheDate) % 7 In (5,6); /* 5 is Saturday and 6 is Sunday */
Select Min(TheDate)
From (Select Top 3 TheDate From @Days Order By TheDate Desc) a;
Tom