IsDateAHoliday UDF **NEW**

,

This is a replacement script for the first IsDateAHoliday. The first one was too slow and inefficient. This one is much better. Enjoy.
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

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

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)