IsDateAHoliday UDF

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 @DayofWeek int
DECLARE @Daily datetime
DECLARE @MLK_Mondays int
DECLARE @Pres_Mondays int
DECLARE @Memorial_Mondays int
DECLARE @Labor_Mondays int
DECLARE @Columbus_Mondays int

DECLARE @MLK int
DECLARE @PresidentsDay int
DECLARE @MemorialDay int
DECLARE @LaborDay int
DECLARE @ColumbusDay int

SELECT @ret = 0
SET @MLK_Mondays = 0
SET @Pres_Mondays = 0
SET @Memorial_Mondays = 0
SET @Labor_Mondays = 0
SET @Columbus_Mondays = 0

SET @DayofWeek = 0
SET @Counter = 0
WHILE @Counter <= 365
BEGIN
SET @Daily = CONVERT(datetime, DATEADD(d, @Counter, '1/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)))
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '1/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2 
BEGIN
-- MLK Day
IF DATEPART(mm, @Daily) = 1
BEGIN
SET @MLK_Mondays = @MLK_Mondays + 1
IF @MLK_Mondays = 3
BEGIN
IF @CurDate = @Daily 
BEGIN
SET @MLK = 1
END
END
END
-- Presidents Day
IF DATEPART(mm, @Daily) = 2
BEGIN
SET @Pres_Mondays = @Pres_Mondays + 1
IF @Pres_Mondays = 3
BEGIN
IF @CurDate = @Daily 
BEGIN
SET @PresidentsDay = 1
END
END
END
-- Labor Day
IF DATEPART(mm, @Daily) = 9
BEGIN
SET @Labor_Mondays = @Labor_Mondays + 1
IF @Labor_Mondays = 1
BEGIN
IF @CurDate = @Daily 
BEGIN
SET @LaborDay = 1
END
END
END
-- Columbus Day
IF DATEPART(mm, @Daily) = 10
BEGIN
SET @Columbus_Mondays = @Columbus_Mondays + 1
IF @Columbus_Mondays = 2
BEGIN
IF @CurDate = @Daily 
BEGIN
SET @ColumbusDay = 1
END
END
END
END
SET @Counter = @Counter + 1
END
-- Memorial Day
SET @Counter = 365
WHILE @Counter > 0
BEGIN
SET @Daily = CONVERT(datetime, DATEADD(d, @Counter, '1/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar)))
SET @DayofWeek = DATEPART(dw, CONVERT(datetime, DATEADD(d, @Counter, '1/1/' + CAST(DATEPART(yyyy, @CurDate) AS varchar))))
IF @DayofWeek = 2 
BEGIN
IF DATEPART(mm, @Daily) = 5
BEGIN
SET @Memorial_Mondays = @Memorial_Mondays + 1
IF @Memorial_Mondays = 1
BEGIN
IF @CurDate = @Daily 
BEGIN
SET @MemorialDay = 1
END
END
END
END
SET @Counter = @Counter - 1
END


SELECT @ret = CASE
-- New years Day
WHEN datepart(mm,@CurDate) = 1 and datepart(dd,@CurDate) = 1 THEN 1
-- MLK Day
WHEN @MLK = 1 THEN 1
-- Presidents Day
WHEN @PresidentsDay = 1 THEN 1
-- Memorial Day
WHEN @MemorialDay = 1 THEN 1
-- Independance Day
WHEN datepart(mm,@CurDate) = 7 and datepart(dd,@CurDate) = 4 THEN 1
-- Labor Day
WHEN @LaborDay = 1 THEN 1
-- Columbus Day
WHEN @ColumbusDay = 1 THEN 1
-- Veterans Day
WHEN datepart(mm,@CurDate) = 11 and datepart(dd,@CurDate) = 11 THEN 1
-- Christmas Eve
WHEN datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 24 THEN 1
-- Christmas Day
WHEN datepart(mm,@CurDate) = 12 and datepart(dd,@CurDate) = 25 THEN 1
ELSE 0
END
RETURN @ret
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO