Holiday Dates Exclusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Holiday Dates Exclusion

Author

Topic

sukh007
Starting Member

2 Posts

Posted - 2009-03-30 : 07:49:27

Hi Guys

I have a stored Procedure that I use that allows me to add a certain number of working days to a selected date. The Code is below

CREATE FUNCTION dbo.ADDWorkingDays
( @StartDate datetime,
@WorkDays int )
RETURNS datetime
AS
BEGIN
DECLARE @TotalDays int, @FirstPart int
DECLARE @EndDate datetime
DECLARE @LastNum int, @LastPart int

IF @WorkDays < 0
BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
END
IF ABS(@WorkDays) < @FirstPart
SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
ELSE
BEGIN
SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5
SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays
SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
END
END

ELSE

BEGIN
SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END
IF @WorkDays < @FirstPart
SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
ELSE
BEGIN
SELECT @TotalDays = (@WorkDays - @FirstPart) / 5
SELECT @LastPart = (@WorkDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays
SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
END
END

RETURN ( @EndDate )

END

This works perfectly for me as I then use this stored procedure in a SQL Trigger as following which adds 5 working days to another field value:

SET @Date_Deadline=(SELECT dbo.AddWorkingDays (@Date_Allocated, 5))

What I now need to do is to exclude Bank Holidays from this so that when it is adding 5 working days it excludes the bank holidays.

Many Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources