{"id":7336,"date":"2014-09-11T17:41:05","date_gmt":"2014-09-11T17:41:05","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/calculating-public-holidays-based-on-the-day-of-the-week\/"},"modified":"2016-07-07T13:34:12","modified_gmt":"2016-07-07T13:34:12","slug":"calculating-public-holidays-based-on-the-day-of-the-week","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/calculating-public-holidays-based-on-the-day-of-the-week\/","title":{"rendered":"Calculating Public Holidays based on the Day of the Week"},"content":{"rendered":"

Occasionally, I like updating old articles that I’ve written on Simple-Talk. Since I used to help Robyn Page with hers, I now and then do the same for her articles as well, with the kind permission of Simple-Talk’s editor. I was rather amused by this DateTime function, that was just derived from one of her examples in her classic ‘Robyn Page’s SQL Server DATE\/TIME Workbench’<\/a>. I extended it to calculate the date, for any year of the first, second, third or fourth weekday (Sunday, Monday, Tuesday etc) in any month. Actually, it will calculate the date of the ‘n’th occurrence of any particular day of the week after the first of the month, even if it occurs in subsequent months. This calculation can cause difficulties because the day of the week is numbered 1-7, and can start at any day, depending on the language setting. To make things more complicated, this setting (@@Datefirst) can be changed independently. No calculations on the day of the week can be dependent on this alarmingly mutable setting. Robyn was struggling with this and asked for my help. I had drunk a rather potent cup of coffee and so, with my temples thumping, I keyed out what seemed a simple expression to get around the problem. I’m not sure that I can explain it now but, mercifully, it seems to work, and works independently of the @@Datefirst setting. I decided to do it as a single query so that the basic expression could also be used ‘in line’ to avoid using a scalar function, but I’ve done it as a scalar function here just to keep things simple. Speed-freaks may prefer to redo it as an inline table-valued function to avoid the performance problems of scalar functions.<\/p>\n

I expect that you will find this function rather simple. If so, would you please do a similar function, independent of your @@DateFirst setting, to calculate such holidays as Arbor Day (Last Friday in April) or Memorial Day (Last Monday in May)<\/p>\n

\r\nIF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL\r\n\u00a0\u00a0 DROP FUNCTION NthDayOfWeekOfMonth\r\nGO\r\n\r\nCREATE FUNCTION NthDayOfWeekOfMonth \r\n\/**\r\nsummary:\u00a0\u00a0 >\r\n\u00a0This is a simple query that calculates the date of holidays that are held on a particular\r\n\u00a0day of the week in a month, such as the first Sunday, third Wednesday and so on. It is\r\n\u00a0designed to give the correct result whatever your definition of the first day of the week,\r\n\u00a0set in the global variable @@DateFirst. This setting determines what is considered the first \r\n\u00a0day of the week. Mysteriously, it is related to language rather than nation or culture.\r\n\u00a0If you alter the language setting, the @@Datefirst value is changed. \r\nAuthor: Phil Factor\r\nRevision: 1.3\r\ndate: 20 May 2014\r\nexample:\r\n\u00a0code: |\r\n\u00a0\u00a0--Martin Luther King Day (Third Monday in January)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Jan','Mon',3)\u00a0\u00a0\r\n\u00a0\u00a0--President's Day (Third Monday in February)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Feb','Mon',3)\u00a0\u00a0\r\n\u00a0\u00a0--\u00a0Mother's Day (Second Sunday in May)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','May','Sun',2)\u00a0\u00a0\r\n\u00a0\u00a0--Father's Day (Third Sunday in June)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Jun','Sun',3)\u00a0\u00a0\r\n\u00a0\u00a0--Labor Day (First Monday in September)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Sep','Mon',1)\u00a0\u00a0\r\n\u00a0\u00a0--Columbus Day (Second Monday in October)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Oct','Mon',2)\u00a0\u00a0\r\n\u00a0\u00a0--Thanksgiving (Fourth Thursday in November)\r\n\u00a0\u00a0Select dbo.NthDayOfWeekOfMonth ('2014','Nov','Thu',4)\u00a0\u00a0\r\n\u00a0\u00a0--Black Friday (Friday after Thanksgiving)\r\n\u00a0\u00a0Select DateAdd(Day,1,dbo.NthDayOfWeekOfMonth ('2014','Nov','Thu',4)) \r\n\r\nreturns:\u00a0\u00a0 >\r\n\u00a0\u00a0The date specified as a DATETIME\r\n**\/\u00a0\u00a0\u00a0\u00a0\r\n(\r\n@TheYear CHAR(4), --the year as four characters (e.g. '2014')\r\n@TheMonth CHAR(3), --in english (Sorry to our EU colleagues) e.g. Jun, Jul, Aug\r\n@TheDayOfWeek CHAR(3), -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun\r\n@Nth INT) --1 for the first date, 2 for the second occurrence, 3 for the third\r\nRETURNS DATETIME\r\nWITH EXECUTE AS CALLER\r\nAS\r\nBEGIN\r\nRETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0)+ (7*@Nth)-1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-(DATEPART (Weekday, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+@@DateFirst+(CHARINDEX(@TheDayOfWeek,'FriThuWedTueMonSunSat')-1)\/3)%7\r\nEND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\nGO\r\n\r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2012','jun','Mon',1))<> '2012-06-04' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (1)',16,1) \r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Sep','Mon',1))<> '2014-09-01' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (2)',16,1) \r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Mar','Wed',1))<> '2015-03-04' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (3)',16,1) \r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('1987','Dec','Sun',1))<> '1987-12-06' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (4)',16,1) \r\n--thanksgiving (4th thursday in november)\r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Nov','Thu',4))<> '2014-11-27' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (5)',16,1) \r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Nov','Thu',4))<> '2015-11-26' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (6)',16,1) \r\n--Father's Day UK (Third Sunday of June)\r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Jun','Sun',3))<> '2014-6-15' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (7)',16,1) \r\nIF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Jun','Sun',3))<> '2015-6-21' \r\n\u00a0\u00a0\u00a0\u00a0RAISERROR('''NthDayOfWeekOfMonth'' stopped working (8)',16,1) \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Occasionally, I like updating old articles that I’ve written on Simple-Talk. Since I used to help Robyn Page with hers, I now and then do the same for her articles as well, with the kind permission of Simple-Talk’s editor. I was rather amused by this DateTime function, that was just derived from one of her……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-7336","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7336","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=7336"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7336\/revisions"}],"predecessor-version":[{"id":22906,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7336\/revisions\/22906"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=7336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=7336"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=7336"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=7336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}