Get Next(Nth) Business date function in SqlServer

When we were working on Insurance project, we got a requirement to process HSBC payments on HSBC business(working) days only. For that HSBC provided us a list of bank holidays for two years. With this list of holidays we created a SQL table' dbo.BankHolidayCalendar'(see the table at the end). Saturday and Sunday are also holidays for Bank.

We have a requirement that, we need to process all the HSBC payments which are 3 days due and 2days in future from today. For this I have written a generic sql function 'dbo.GetBusinessDateFrom'. This function requires 2 i/p parameters

1. @currentDate - Ref Date i.e., from Which date do you require Nth Business date
2. @businessDays - Ex.(2, 3, -2,-4, N etc) From above Ref date to how many days you need go forward or back ward(Negative number indicates back wards)

By using below sql function we can find the future Nth business date and past Nth business date. To find the past Nth business date we need to give negative number as input parameter for @businessDays

To find whether the date is Saturday Or Sunday , I have used the built in sql function
DatePart(dw,@date) - Dw for the Day of the Week. If you want know more about this function
http://support.microsoft.com/kb/186265. As i'm taking Saturday and Sunday are also bank holidays I shoudn't count these 2 days for that i'm using datepart(dw,@nextBusinessDate) not in (1,7)

Ex: How to Use/ Call the below function
Getting 3rd BusinessDate from today -> dbo.GetBusinessDateFrom(GETDATE(),3)
Getting past 3rd business date from today ->dbo.GetBusinessDateFrom(GETDATE(),-3)


/****** Object: UserDefinedFunction [dbo].[GetBusinessDateFrom] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[GetBusinessDateFrom]
(
@currentDate as DateTime,
@businessDays as int
)
RETURNS DateTime
AS

BEGIN

declare @businessDaysCount int;
declare @isBankHoliday bit;
declare @progress int;

set @businessDaysCount=0
set @isBankHoliday=0;

declare @nextBusinessDate DateTime
set @nextBusinessDate = @currentDate

if(@currentDate is null)
return null

if (@businessDays>=0)
set @progress=1;
else
set @progress=-1;


while((@businessDays>=0 and @businessDaysCount<@businessDays) or (@businessDaysCount>@businessDays))
begin
set @nextBusinessDate=dbo.GetDateWithoutTime(dateadd(day,@progress,@nextBusinessDate))
select @isBankHoliday=count(*) from BankHolidayCalendar where HolidayDate=@nextBusinessDate
if (datepart(dw,@nextBusinessDate) not in (1,7) and @isBankHoliday!=1)
begin
if(@businessDays>=0)
set @businessDaysCount=@businessDaysCount+1
else
set @businessDaysCount=@businessDaysCount-1
end
end

return @nextBusinessDate
END


Below is the sql table contains list of bank holidays
ID HolidayDate Description
1 2008-01-01 New Year's Day
2 2008-03-21 Good Friday
3 2008-05-19 Victoria Day
4 2008-07-01 Canada Day
5 2008-08-04 B.C. Day

6 2008-09-01 Labor Day
7 2008-10-13 Thanksgiving Day
8 2008-11-11 Remembrance Day

9 2008-12-25 Christmas Day
10 2008-12-26 Boxing Day
11 2009-01-01 New Year's Day
12 2009-04-10 Good Friday
13 2009-05-18 Victoria Day
14 2009-07-01 Canada Day
15 2009-08-03 B.C. Day
16 2009-09-07 Labor Day
17 2009-10-12 Thanksgiving Day
18 2009-11-11 Remembrance Day
19 2009-12-25 Christmas Day
20 2009-12-26 Boxing Day

GetDateWitoutTime -- User Defined SQL Function - SqlServer

/****** Object: UserDefinedFunction [dbo].[GetDateWithoutTime] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE FUNCTION [dbo].[GetDateWithoutTime]
(
@Date as DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @dateString varchar(30)
SET @dateString = CAST(YEAR(@Date ) as varchar) + RIGHT('00'+CAST(MONTH(@Date ) as varchar), 2) + RIGHT('00'+CAST(DAY(@Date ) as varchar), 2)
return cast(@dateString as DateTime)
END