IsDateAHoliday UDF **NEW**

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joe Colletti » IsDateAHoliday UDF **NEW**

IsDateAHoliday UDF **NEW**

Rate Topic

Display Mode

Topic Options

Author

Message

Joe Colletti

Posted Friday, October 05, 2007 2:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 31, 2005 11:55 AM
Points: 2, Visits: 1

Comments posted to this topic are about the item IsDateAHoliday UDF **NEW**

Post #407225

steve folkerts

Posted Wednesday, May 14, 2008 1:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:48 PM
Points: 4, Visits: 144

A function to return holidays is a great idea. However, I used this function and found that Nov 24,2008 returns true (1) as a Holiday. November 24 is a Monday, and Thanksgiving is the 4th Thursday in November.

The TSQL can be simplified. My function is below (with some different holiday dates, change as needed). I set variables for the month, day of week, day of month and use those to determine whether the date is a holiday.
--==============================================================
CREATE FUNCTION [dbo].[udf_DateIsHoliday]
(
@DateIn datetime
)
RETURNS bit
AS
BEGIN
DECLARE @mm tinyint;
DECLARE @dw tinyint;
DECLARE @dd tinyint;
SET @mm = DATEPART(mm,@DateIn);
SET @dw = DATEPART(dw,@DateIn);
SET @dd = DATEPART(dd,@DateIn);

-- January 1
IF(@mm = 1 and @dd = 1)
RETURN 1;

--MemorialDay
-- Last Monday in May
IF(@mm = 5 and @dw = 2 and @dd >= 25)
RETURN 1;

-- July 4
IF(@mm = 7 and @dd = 4)
RETURN 1;

--Labor Day
-- first Monday in September
IF(@mm = 9 and @dw = 2 and @dd <= 7)
RETURN 1;

-- Thanksgiving
-- 4th Thursday of Nov
IF(@mm = 11 and @dw = 5 and @dd >= 22 and @dd <= 28)
RETURN 1;

-- Day After Thanksgiving
IF(@mm = 11 and @dw = 6 and @dd >= 23 and @dd <= 29)
RETURN 1;

-- DEC 24
IF(@mm = 12 and @dd = 24)
RETURN 1;

-- DEC 25
IF(@mm = 12 and @dd = 25)
RETURN 1;

RETURN 0;
END
--==============================================================

Post #500874

« Prev Topic | Next Topic »

Permissions