IsDateAHoliday UDF **NEW**

  • Joe Colletti

    Grasshopper

    Points: 20

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

  • steve folkerts

    SSC Rookie

    Points: 46

    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

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply