Transact-SQL: calculating 31 days (excluding business days & holidays)

[eshie]

Hi,

I am trying to create a function to calculate & return a datetime of 31 days from any given date. I want to exclude weekends & holidays (I have created a holidays table with specific dates). The code below returns a datetime of exactly 31 days from given date. variable: @dcndate is the date to evaluate & calculate from. variable: @hmodate is the date I want returned (31 days from dcndate - excluding holidays/weekends). Can anyone help? I would be so appreciative...

Code:
CREATE FUNCTION FN_HMO_INTEREST
(
@StartDay DATETIME
)
RETURNS DATETIME

AS

BEGIN

DECLARE @hmodate DATETIME,
@dcndate DATETIME,
@count INT,
@day INT


SET @dcndate = @StartDay
SET @count = 0

/* Process till @dcndate becomes @hmodate */

WHILE (
DATEDIFF(dd,@dcndate,@hmodate) >= 0
)
BEGIN
SELECT @day = DATEPART(dw,@dcndate)

/* If it is not a Saturday or a Sunday */
IF (@day != 1 AND @day != 7)
/* If it is not a Public Holiday */
IF NOT EXISTS
(
SELECT *
FROM TBL_HOLIDAYS
WHERE holiday= @dcndate
)
/* Increment @dcndate by 31 day */

SELECT @hmodate = DATEADD(dd,31,@dcndate)

END

RETURN @hmodate

END

 

[vongrunt]

IMO the best way to do that is with "calendar" table populated with all dates in near future, not just holidays.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)