IsDateAHoliday UDF **NEW**

• Joe Colletti

  Grasshopper

  Points: 20

  More actions

  October 5, 2007 at 2:40 am

  #179895

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

• steve folkerts

  SSC Rookie

  Points: 46

  More actions

  May 14, 2008 at 1:56 pm

  #815523

  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

  --==============================================================