Which Thursday is the Holiday?

  • Comments posted to this topic are about the item Which Thursday is the Holiday?

  • For a lot of companies in the US - the day after Thanksgiving is also considered a holiday.  Another question would be - how would you code to get that day as a holiday?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'd always use a calendar table, which lets me mark random days (or inconsistent ones) as holidays and join to that table 😉


  • Steve has the better answer, but if you want to do it in code - compute the 4th Thursday then add one day to it.

    declare @d date; set @d = cast('20231123' as date)

    When Month(@d) = 11
    AND DATEPART(weekday, @d) = 5 --Thursday
    and day(@d) > 21
    and day(@d) <= 28
    Then 1

    --Thanksgiving Friday
    When Month(@d) = 11
    AND DATEPART(weekday, @d) = 6 --Friday
    and day(@d) - 1 > 21
    and day(@d) - 1 <= 28
    Then 1
    Else 0
    End as "IsHoliday"

    I don't have SSMS on this computer, so there may be a minor typing mistake (however, it was verified on a computer that has SSMS)

  • The following isn't dependent on DATEFIRST at all.

    DECLARE @AnyYear INT = 2023;
    SELECT ThanksGiving = v1.TG
    ,BlackFriday = DATEADD(dd,1,v1.TG)
    FROM (VALUES(DATEADD(dd,DATEDIFF(dd,3,DATEFROMPARTS(@AnyYear,11,28))/7*7,3)))v1(TG)

    I agree that Calendar table work well for several different things (especially holidays and business day calculations).  Contrary to Kimball, I don't like using an integer as the key on a Calendar table. especially but not limited to when it's a true surrogate key for a date.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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