IsDateAHoliday UDF

,

This is a User Defined Function that returns a 1 if date is a holiday else, it returns a 0.
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

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

Rate

4 (1)

Share

Share

Rate

4 (1)