{"id":8511,"date":"2015-11-24T12:30:26","date_gmt":"2015-11-24T12:30:26","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-sql-of-the-feast-days-of-western-christendom\/"},"modified":"2016-07-07T11:50:47","modified_gmt":"2016-07-07T11:50:47","slug":"the-sql-of-the-feast-days-of-western-christendom","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-sql-of-the-feast-days-of-western-christendom\/","title":{"rendered":"The SQL of the Feast Days of Western Christendom"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2015\/11\/saints.jpg\" width=\"320\" height=\"205\" alt=\"saints.jpg\" \/><\/p>\n<p>I&#8217;ve been intrigued for a long time by the Christian feasts. Nowadays, we start getting excited about Christmas around July, and it is considered by commerce to be fair-game as a de-walletizing activity from Halloween onwards. I would like far more feast days to get excited about. Our ancestors had so many feast days that they didn&#8217;t bother with dates. Any day could be\u00a0 described by it&#8217;s saint, feast or remembrance. Dates were generally spoken of by their proximity to the nearest feast day. Now we have boring national days that nobody really feels like celebrating unless it means a day off work. Every day in the year is celebrated somewhere in Christendom by at least one saint. Some days have five or six candidates depending on the nation or region. When Europe was christianised, the policy was the &#8216;light touch&#8217;. The existing gods and ancestral figures were merely made into saints. Regions that had a different ancestral figure for each extended family ended up with thousands of saints. This was definitely promotion<\/p>\n<p>Why stop at the western Christian church? The calculation of Ramadan is very difficult to pin down and the Islamic Hijri calendar<a href=\"https:\/\/en.wikipedia.org\/wiki\/Tabular_Islamic_calendar&#035;Kuwaiti_algorithm\">  is difficult to calculate<\/a> from the Gregorian calendar as it is based on observation and astronomical calculation. The Hindu feast days depend on which of eight calendars you choose (Only the names of the twelve months are the same though not in the same order). Apologies for sticking with Western Christianity.<\/p>\n<p>I&#8217;ve been determined to produce a SQL Expression that was able to tell you when all the feasts and saints days are. In the following example, I&#8217;ve only put the major feast days that were generally celebrated in Britain before the reformation, but it is very easy to add or take away what I&#8217;ve given you to taste. There are two main ways of determining the date of a feast. The easiest ones happen on the same date every year. The most important ones are the movable feasts, based on Easter. They all take place a fixed number of days from Easter Sunday. <a href=\"http:\/\/www.tertullian.org\/fathers\/dionysius_exiguus_easter_01.htm&#035;cycle\"> The calculation of Easter<\/a>, the so-called &#8216;Computus&#8217;, isn&#8217;t easy. It\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/Computus\">varies  by over 40 days<\/a>\u00a0since it depends partly on the\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/Lunisolar_calendar\">phase  of the moon<\/a>. Whole books have been written on the subject. Finally, there are the anomalous ones, such as Advent, which takes place on the nearest sunday to St Andrews&#8217; day, which is a fixed date. Advent therefore will vary over seven days.<\/p>\n<p>I wanted to be able to produce a calendar of dates for as many years as you wanted in one SQL Expression. I thought I&#8217;d produce something that used the date features that were introduced in SQL Server 2012. With a little refactoring you can get something that will work in SQL Server 2005.\u00a0 Here is what I wrote.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \" >\r\n WITH\r\n \u00a0 Years (Yr)\r\n \u00a0 AS\r\n \u00a0 (\/* choose your range of years here in the CTE expression. I've done as a sample last year\r\n \u00a0 this year and next year, but you can alter it to whatever list of years you wish *\/\r\n \u00a0-- SELECT yr FROM (VALUES (2014),(2015),(2016))f(Yr)\r\n \u00a0 SELECT yr FROM\r\n \u00a0\u00a0\u00a0\u00a0 (VALUES \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (DatePart(Year,GetDate())-1),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (DatePart(Year,GetDate())),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0(DatePart(Year,GetDate())+1)\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0)f(Yr)\r\n \u00a0 )\r\n \/* first select the 'movable feasts' that have a fixed day from easter. To do this\r\n we calculate the date of easter using the 'Computus' using the formula for paschal\r\n full moon date and is valid from 1900 to 2199. All the 'movable feasts' are then \r\n calculated from easter, except for Advent.*\/\r\n SELECT\u00a0 dateadd(DAY, DaysFromEaster, Easter_Day) AS TheDate, Festival\r\n FROM ( VALUES --the number of days from easter, and the name of the feast day\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( -63, 'Septuagesima Sunday'), ( -56, 'Sexagesima Sunday'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( -49, 'Quinquagesima Sunday'), ( -47, 'Shrove Tuesday or Mardi Gras'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( -46, 'Ash Wednesday'), ( -7, 'Palm Sunday'), ( -3, 'Maundy Thursday'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( -2, 'Good Friday'), ( 0, 'Easter sunday'), ( 49, 'Pentecost'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 35, 'Rogation'), ( 36, 'Rogation Monday'), ( 37, 'Rogation Tuesday'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 38, 'Rogation Wednesday'), ( 39, 'Ascension'), ( 49, 'Whitsunday (Pentecost)'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 56, 'Trinity Sunday'), ( 60, 'Corpus Christi') \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0) \r\n AS Category ( DaysFromEaster, Festival )\r\n CROSS JOIN --easter day for each year\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT datefromparts(Yr, [day] \/ 31, ( [day] % 31 ) + 1) AS Easter_Day\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 FROM\u00a0\u00a0 \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 ( SELECT\u00a0 \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Yr, ( ( 19 * ( Yr % 19 ) + ( Yr \/ 100 ) - ( ( Yr \/ 100 ) \/ 4 ) - ( ( ( Yr \/ 100 ) - ( ( ( Yr \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) + ( ( 32 + 2 * ( ( Yr \/ 100 ) % 4 ) + 2 * ( ( Yr % 100 ) \/ 4 ) - ( ( 19 * ( Yr % 19 ) + ( Yr \/ 100 ) - ( ( Yr \/ 100 ) \/ 4 ) - ( ( ( Yr \/ 100 ) - ( ( ( Yr \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) - ( ( Yr % 100 ) % 4 ) ) % 7 ) - 7 * ( ( ( Yr % 19 ) + 11 * ( ( 19 * ( Yr % 19 ) + ( Yr \/ 100 ) - ( ( Yr \/ 100 ) \/ 4 ) - ( ( ( Yr \/ 100 ) - ( ( ( Yr \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) + 22 * ( ( 32 + 2 * ( ( Yr \/ 100 ) % 4 ) + 2 * ( ( Yr % 100 ) \/ 4 ) - ( ( 19 * ( Yr % 19 ) + ( Yr \/ 100 ) - ( ( Yr \/ 100 ) \/ 4 ) - ( ( ( Yr \/ 100 ) - ( ( ( Yr \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) - ( ( Yr % 100 ) % 4 ) ) % 7 ) ) \/ 451 ) + 114 AS [Day]\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0FROM\u00a0\u00a0\u00a0 \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 years\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 ) g \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) E\r\n UNION ALL \r\n SELECT\u00a0 datefromparts( yr, TheMonth, TheDay), Festival\r\n FROM -- these are easy to do and we all just use the DateFromParts function now\r\n \u00a0 ( VALUES \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 1,\u00a0 1, 'The Circumcision of Christ'), ( 6, 1, 'Epiphany'), \r\n <span class=\"style1\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span> ( 13, 1, 'Day of St Hilary the Bishop'), ( 25, 1, 'The Conversion of St Paul'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 2,\u00a0 2, 'The Purification of the Blessed Virgin Mary and the Presentation of Christ in the Temple (Candlemas)'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 14, 2, 'St Valentine''s Day'), ( 24, 2, 'St Matthias'' Day'), ( 1, 3, 'St David''s Day'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 12, 3, 'St Gregory''s Day'), ( 17, 3, 'St Patrick''s Day'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 18, 3, 'The Day of St Edward, King of the West Saxons'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 25, 3, 'Annunciation of the Virgin (Lady Day or Ladymas)'), ( 4, 4, 'St Ambrose''s Day'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 23, 4, 'St George''s Day'), ( 25, 4, 'St Mark''s day'), ( 1, 5, 'The Day of St Philip and St James the Less.'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 26, 5, 'The Day of St Augustine, first Archbishop of Canterbury'), ( 24, 6, 'The Nativity of St John Baptist'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 29, 6, 'The Day of St Peter and St Paul'), ( 2, 7, 'The Visitation of the Blessed Virgin Mary'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 22, 7, 'St Mary Magdalene''s Day'), ( 25, 7, 'The Day of St James the Apostle'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 6,\u00a0 8, 'The Transfiguration'), ( 10, 8, 'St Lawrence''s day'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 13, 8, 'Lammas Day (from 1753- 1st August before then)'), ( 24, 8, 'The Day of St Bartholomew the Apostle'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 8,\u00a0 9, 'The Nativity of the Virgin Mary'), ( 14, 9, 'Holy Cross Day (Holy Rood Day or Roodmas)'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 21, 9, 'St Matthew the Apostle'), ( 29, 9, 'St Michael and All Angels (Michaelmas)'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 30, 9, 'St Jerome''s day'), ( 18, 10, 'St Luke''s day'), ( 25, 10, 'St Crispin''s day'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 28, 10,'St Simon and St Jude the Apostles'), ( 1, 11, 'All Saints (Hallowmas or AllHallows)'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 2,\u00a0 11,'All Souls'' Day'), ( 11, 11, 'St Martin''s day (Martinmas)'), ( 22, 11, 'St Cecilia''s day'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 30, 11,'St Andrew''s day'), ( 6, 12, 'St Nicholas''s day'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 8,\u00a0 12,'The Conception of the Blessed Virgin Mary'), ( 21, 12, 'The Day of St Thomas the Apostle'),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 25, 12,'Christmas Day'), ( 26, 12, 'The Feast of St Stephen'), ( 27, 12, 'The Day of St John the Evangelist'), \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( 28, 12,'Holy Innocents Day (Childermas)'), ( 29, 12, 'The Day of St Thomas a Becket') \r\n \u00a0 ) f ( TheDay, TheMonth, Festival ) \r\n \u00a0 CROSS JOIN Years\r\n UNION ALL\r\n SELECT \/* Advent sunday is the sunday nearest to St Andrews' Day*\/\r\n \u00a0 dateadd\r\n \u00a0\u00a0\u00a0 (DAY,\r\n \u00a0\u00a0\u00a0 CASE WHEN Daydifference &lt; 4 THEN -DayDifference ELSE 7 - Daydifference\u00a0 END, StAndrewsDay), \r\n \u00a0\u00a0\u00a0 'Advent'\r\n FROM\r\n \u00a0 (SELECT \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0datediff(DAY, '01 Jan 1899', datefromparts( Yr, 11, 30)) % 7 AS DayDifference, \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 datefromparts( Yr, 11, 30) AS StAndrewsDay\r\n \u00a0\u00a0\u00a0 from Years\r\n \u00a0)f\r\n \u00a0ORDER BY TheDate\r\n<\/pre>\n<p>This gives (just for the one year,\u00a0 in which I wrote it)<\/p>\n<ul class=\"list--tight\">\n<li>2015-01-01 The Circumcision of Christ<\/li>\n<li>2015-01-06 Epiphany<\/li>\n<li>2015-01-13 Day of St Hilary the Bishop<\/li>\n<li>2015-01-25 The Conversion of St Paul<\/li>\n<li> 2015-02-01 Septuagesima Sunday<\/li>\n<li>2015-02-02 The Purification of the Blessed Virgin Mary etc.<\/li>\n<li> 2015-02-08 Sexagesima Sunday<\/li>\n<li>2015-02-14 St Valentine&#8217;s Day<\/li>\n<li>2015-02-15 Quinquagesima Sunday<\/li>\n<li>2015-02-17 Shrove Tuesday or Mardi Gras<\/li>\n<li>2015-02-18 Ash Wednesday<\/li>\n<li>2015-02-24 St Matthias&#8217; Day<\/li>\n<li>2015-03-01 St David&#8217;s Day<\/li>\n<li> 2015-03-12 St Gregory&#8217;s Day<\/li>\n<li>2015-03-17 St Patrick&#8217;s Day<\/li>\n<li>2015-03-18 The Day of St Edward, King of the West Saxons<\/li>\n<li>2015-03-25 Annunciation of the Virgin (Lady Day or Ladymas)<\/li>\n<li>2015-03-29 Palm Sunday<\/li>\n<li>2015-04-02 Maundy Thursday<\/li>\n<li> 2015-04-03 Good Friday<\/li>\n<li>2015-04-04 St Ambrose&#8217;s Day<\/li>\n<li>2015-04-05 Easter sunday<\/li>\n<li>2015-04-23 St George&#8217;s Day<\/li>\n<li>2015-04-25 St Mark&#8217;s day<\/li>\n<li>2015-05-01 The Day of St Philip and St James the Less.<\/li>\n<li>2015-05-10 Rogation<\/li>\n<li>2015-05-11 Rogation Monday<\/li>\n<li>2015-05-12 Rogation Tuesday<\/li>\n<li>2015-05-13 Rogation Wednesday<\/li>\n<li> 2015-05-14 Ascension<\/li>\n<li>2015-05-24 Whitsunday (Pentecost)<\/li>\n<li>2015-05-24 Pentecost<\/li>\n<li>2015-05-26 The Day of St Augustine, first Archbishop of Canterbury<\/li>\n<li> 2015-05-31 Trinity Sunday<\/li>\n<li>2015-06-04 Corpus Christi<\/li>\n<li>2015-06-24 The Nativity of St John Baptist<\/li>\n<li>2015-06-29 The Day of St Peter and St Paul<\/li>\n<li> 2015-07-02 The Visitation of the Blessed Virgin Mary<\/li>\n<li>2015-07-22 St Mary Magdalene&#8217;s Day<\/li>\n<li>2015-07-25 The Day of St James the Apostle<\/li>\n<li>2015-08-06 The Transfiguration<\/li>\n<li>2015-08-10 St Lawrence&#8217;s day<\/li>\n<li>2015-08-13 Lammas Day (from 1753- 1st August before then)<\/li>\n<li>2015-08-24 The Day of St Bartholomew the Apostle<\/li>\n<li>2015-09-08 The Nativity of the Virgin Mary<\/li>\n<li>2015-09-14 Holy Cross Day (Holy Rood Day or Roodmas)<\/li>\n<li>2015-09-21 St Matthew the Apostle<\/li>\n<li> 2015-09-29 St Michael and All Angels (Michaelmas)<\/li>\n<li>2015-09-30 St Jerome&#8217;s day<\/li>\n<li> 2015-10-18 St Luke&#8217;s day<\/li>\n<li>2015-10-25 St Crispin&#8217;s day<\/li>\n<li>2015-10-28 St Simon and St Jude the Apostles<\/li>\n<li>2015-11-01 All Saints (Hallowmas or AllHallows)<\/li>\n<li> 2015-11-02 All Souls&#8217; Day<\/li>\n<li>2015-11-11 St Martin&#8217;s day (Martinmas)<\/li>\n<li> 2015-11-22 St Cecilia&#8217;s day<\/li>\n<li>2015-11-29 Advent<\/li>\n<li>2015-11-30 St Andrew&#8217;s day<\/li>\n<li> 2015-12-06 St Nicholas&#8217;s day<\/li>\n<li>2015-12-08 The Conception of the Blessed Virgin Mary<\/li>\n<li>2015-12-21 The Day of St Thomas the Apostle<\/li>\n<li>2015-12-25 Christmas Day<\/li>\n<li> 2015-12-26 The Feast of St Stephen<\/li>\n<li>2015-12-27 The Day of St John the Evangelist<\/li>\n<li>2015-12-28 Holy Innocents Day (Childermas)<\/li>\n<li>2015-12-29 The Day of St Thomas a Becket<\/li>\n<\/ul>\n<p>So there you have it. Plenty of potential feasts to celebrate there!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve been intrigued for a long time by the Christian feasts. Nowadays, we start getting excited about Christmas around July, and it is considered by commerce to be fair-game as a de-walletizing activity from Halloween onwards. I would like far more feast days to get excited about. Our ancestors had so many feast days that&#8230;&hellip;<\/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-8511","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\/8511","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=8511"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8511\/revisions"}],"predecessor-version":[{"id":22898,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8511\/revisions\/22898"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8511"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8511"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8511"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}