You asked: Thanksgiving in SQL

From the logs: how do you calculate Thanksgiving in MS SQL? For that, first a background reminder from Wikipedia. There are of course two kinds of Thanksgiving: A Canadian one (which is the second Monday of October) and the US one (the 4th week of November). The good news is that I’ve got the queries right here, so you can copy and paste it right in your SQL code.

The Canadian one is the base query: basically, I pick out the first Monday of October first and add 1 extra week to it:

select DATEADD(wk, 1, 
    DATEADD(wk, 
      DATEDIFF(wk,0,
      CONVERT(datetime, '10/01/' 
       + cast(DATEPART(yy, getdate()) as varchar(4)), 
       101) ), 0
           ) )

The US query is based on the one above: First I get the very first Monday of November, I add 3 days to that, and add another 3 weeks to the last DateAdd function, et voila.

select DATEADD(ww, 3,
     DATEADD(
     dd, 3,
     DATEADD(wk, 0, 
    DATEADD(wk, 
      DATEDIFF(wk,0,
      CONVERT(datetime, '11/01/' 
       + cast(DATEPART(yy, getdate()) as varchar(4)), 
       101) ), 0
           ) ) ))

Mind the bad code formatting.