Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IsDateAHoliday UDF **NEW** Expand / Collapse
Author
Message
Posted Friday, October 5, 2007 2:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, May 14, 2008 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:21 PM
Points: 4, Visits: 163
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 »

Add to briefcase

Permissions Expand / Collapse