{"id":1304,"date":"2006-10-20T20:42:38","date_gmt":"2006-10-20T23:42:38","guid":{"rendered":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1304"},"modified":"2006-10-22T20:32:28","modified_gmt":"2006-10-22T23:32:28","slug":"you-asked-thanksgiving-in-sql","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1304","title":{"rendered":"You asked: Thanksgiving in SQL"},"content":{"rendered":"<p><span class=\"dropcap\">F<\/span>rom the logs: how do you calculate Thanksgiving in MS SQL? For that, first a background reminder from <a href=\"http:\/\/en.wikipedia.org\/wiki\/Thanksgiving\">Wikipedia<\/a>. 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&#8217;ve got the queries right here, so you can copy and paste it right in your SQL code.\n<\/p>\n<p>The Canadian one is the base query: basically, I pick out the first Monday of October first and add 1 extra week to it:<\/p>\n<pre>\r\nselect DATEADD(wk, 1, \r\n    DATEADD(wk, \r\n      DATEDIFF(wk,0,\r\n      CONVERT(datetime, '10\/01\/' \r\n       + cast(DATEPART(yy, getdate()) as varchar(4)), \r\n       101) ), 0\r\n           ) )\r\n<\/pre>\n<\/p>\n<p>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.<\/p>\n<pre>\r\nselect DATEADD(ww, 3,\r\n     DATEADD(\r\n     dd, 3,\r\n     DATEADD(wk, 0, \r\n    DATEADD(wk, \r\n      DATEDIFF(wk,0,\r\n      CONVERT(datetime, '11\/01\/' \r\n       + cast(DATEPART(yy, getdate()) as varchar(4)), \r\n       101) ), 0\r\n           ) ) ))\r\n<\/pre>\n<\/p>\n<p>Mind the bad code formatting.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip; <a href=\"http:\/\/www.hoogervorst.ca\/arthur\/?p=1304\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[40],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1304"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1304"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1304\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1304"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}