IsDateAHoliday UDF **NEW**

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE   function dbo.IsDateAHoliday(@CurDate datetime) 
returns int as
BEGIN
-- --------------------------------------------------------------------
-- IsDateAHoliday is a User Defined Function that
-- accepts a date and returns a 1 if date is a national holiday
-- and returns a 0 otherwise
-- The holidays are:
-- New years Day, MLK Day, Presidents Day, Memorial Day, Independance Day, 
-- Labor Day, Columbus Day, Veterans Day, Christmas Eve and Christmas Day
-- Developed by Joe Colletti for Webster Bank
-- --------------------------------------------------------------------
DECLARE @counter int
DECLARE @ret int
DECLARE @Month int
DECLARE @DayofWeek int

SET @ret = 0
SET @Month = DATEPART(mm, @CurDate)

-- New Years Day
IF datepart(mm,@CurDate) = 1 and datepart(dd,@CurDate) = 1
SET @ret = 1
-- MLK Day (3rd Monday in January - 15th to 21st)
IF @Month = 1
BEGIN
SET @Counter = 14
WHILE @Counter <= 20
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '1/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '1/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Presidents Day (3rd Monday in February - 15th to 21st)
IF @Month = 2
BEGIN
SET @Counter = 14
WHILE @Counter <= 20
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '2/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '2/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Memorial Day (Last Monday in May - 25th to 31st)
IF @Month = 5
BEGIN
SET @Counter = 24
WHILE @Counter <= 30
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '5/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '5/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Independance Day
IF datepart(mm,@CurDate) = 7 and datepart(dd,@CurDate) = 4
SET @ret = 1
-- Labor Day (1st Monday in September - 1st to 7th)
IF @Month = 9
BEGIN
SET @Counter = 0
WHILE @Counter <= 6
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '9/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '9/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Columbus Day (2nd Monday in October - 8th to 14th)
IF @Month = 10
BEGIN
SET @Counter = 7
WHILE @Counter <= 13
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '10/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '10/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Thanksgiving Day (4th Thursday in November - 22nd to 28th)
IF @Month = 11
BEGIN
SET @Counter = 21
WHILE @Counter <= 27
BEGIN
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '11/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2
BEGIN
IF CONVERT(datetime, '11/' + CAST(@Counter + 1 AS varchar) + '/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)) = @CurDate
BEGIN
SET @ret = 1
END
END
SET @Counter = @Counter + 1
END
END
-- Christmas Eve
IF datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 24
SET @ret = 1
-- Christmas Day
IF datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 25
SET @ret = 1

RETURN @ret
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO