How to get two business days prior to some date field (Settlement Date)

Question

Hi Everyone,
I am trying to get two business days prior to my Date Field (in this case, Settlement Date)
For example, I have a Settlement Date of 12/28/2020. Two business days prior to 12/28/2020 would be 12/23/2020.

What I have done so far is:

1. DATEADD(d,-2,SettlementDate) This gives me 12/26/2020
2. I checked if 12/26/2020 is Saturday or Sunday. In this case, 12/26/2020 is Saturday. So, I go backward 1 more day and it is now 12/25/2020
3. I checked if 12/25/2020 is a holiday, in this case it is. So, I go backward 1 more day again and it is now 12/24/2020

But, 12/24/2020 is really not 2 business days prior to 12/28/2020. It is only one day.

I may approach this issue the wrong way.

Thank you advance for any help.

Answer 1

Hi @Villa ,

Thank you so much for posting here in Microsoft Q&A.

It is recommended for you to create a dim calendar table with all dates including weekends and holidays defined. Refer Designing a Calendar Table.

Then you could proceed with get the business days prior or after to the some date field (Settlement Date) using one user-defined function.

Please also refer below a simple example and check whether it is helpful to you.

DECLARE @businessday DATETIME,@Settlementdate DATETIME  
  
DECLARE @TblHoliday AS TABLE (Holiday_Date DATE)  
  
INSERT INTO @TblHoliday --add more holidays here  
VALUES ('2020-11-26')  
    ,('2020-12-25')  
  
SET @Settlementdate = '2020-12-28'; --define the Settlement Date here  
  
DECLARE @N INT  
DECLARE @I INT  
SET @N=2  --2 business days prior  
SET @I=1  
  
WHILE @I<=@N  
BEGIN  
SET @businessday = dateadd(day,  -1, @Settlementdate)  
  
WHILE ((SELECT TOP 1 1  
            FROM @TblHoliday  
            WHERE Holiday_Date = @businessday) = 1  
        OR datename(dw, @businessday) IN ('Saturday', 'Sunday'))  
	BEGIN  
		SET @businessday = dateadd(day, - 1, @businessday)  
	END  
  
	SET @I=@I+1  
	SET @Settlementdate=@businessday  
END  
  
SELECT @businessday  

Output:

2020-12-23 00:00:00.000  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You need to use a calendar table. See "udf_AddBusinessDays"

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/