Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 IsDateAHoliday UDF **NEW** Rate Topic Display Mode Topic Options
Author
 Message
 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
 Posted Wednesday, May 14, 2008 1:56 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 03, 2013 1:32 PM Points: 4, Visits: 148
 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 bitASBEGINDECLARE @mm tinyint;DECLARE @dw tinyint;DECLARE @dd tinyint;SET @mm = DATEPART(mm,@DateIn);SET @dw = DATEPART(dw,@DateIn);SET @dd = DATEPART(dd,@DateIn);-- January 1IF(@mm = 1 and @dd = 1) RETURN 1; --MemorialDay-- Last Monday in MayIF(@mm = 5 and @dw = 2 and @dd >= 25) RETURN 1;-- July 4IF(@mm = 7 and @dd = 4) RETURN 1;--Labor Day-- first Monday in SeptemberIF(@mm = 9 and @dw = 2 and @dd <= 7) RETURN 1;-- Thanksgiving-- 4th Thursday of NovIF(@mm = 11 and @dw = 5 and @dd >= 22 and @dd <= 28) RETURN 1;-- Day After ThanksgivingIF(@mm = 11 and @dw = 6 and @dd >= 23 and @dd <= 29) RETURN 1;-- DEC 24IF(@mm = 12 and @dd = 24) RETURN 1;-- DEC 25IF(@mm = 12 and @dd = 25) RETURN 1;RETURN 0;END--==============================================================
Post #500874

 Permissions