Which Thursday is the Holiday?

Question

Which Thursday is the Holiday?

Steve Jones - SSC Editor

SSC Guru

Points: 735513
More actions
November 24, 2023 at 12:00 am

#4324622

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

December 19, 2023 at 3:23 am

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

Jeffrey Williams SSC Guru Points: 90285 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 735513 More actions · Answer 2

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

JChrisCompton SSCommitted Points: 1609 More actions · Answer 3

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)

SELECT 
Case
   --Thanksgiving
   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)

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 4

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)