I’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’t bother with dates. Any day could be described by it’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 ‘light touch’. 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
Why stop at the western Christian church? The calculation of Ramadan is very difficult to pin down and the Islamic Hijri calendar is difficult to calculate 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.
I’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’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’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. The calculation of Easter, the so-called ‘Computus’, isn’t easy. It varies by over 40 days since it depends partly on the phase of the moon. 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’ day, which is a fixed date. Advent therefore will vary over seven days.
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’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. Here is what I wrote.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
WITH Years (Yr) AS (/* choose your range of years here in the CTE expression. I've done as a sample last year this year and next year, but you can alter it to whatever list of years you wish */ -- SELECT yr FROM (VALUES (2014),(2015),(2016))f(Yr) SELECT yr FROM (VALUES (DatePart(Year,GetDate())-1), (DatePart(Year,GetDate())), (DatePart(Year,GetDate())+1) )f(Yr) ) /* first select the 'movable feasts' that have a fixed day from easter. To do this we calculate the date of easter using the 'Computus' using the formula for paschal full moon date and is valid from 1900 to 2199. All the 'movable feasts' are then calculated from easter, except for Advent.*/ SELECT dateadd(DAY, DaysFromEaster, Easter_Day) AS TheDate, Festival FROM ( VALUES --the number of days from easter, and the name of the feast day ( -63, 'Septuagesima Sunday'), ( -56, 'Sexagesima Sunday'), ( -49, 'Quinquagesima Sunday'), ( -47, 'Shrove Tuesday or Mardi Gras'), ( -46, 'Ash Wednesday'), ( -7, 'Palm Sunday'), ( -3, 'Maundy Thursday'), ( -2, 'Good Friday'), ( 0, 'Easter sunday'), ( 49, 'Pentecost'), ( 35, 'Rogation'), ( 36, 'Rogation Monday'), ( 37, 'Rogation Tuesday'), ( 38, 'Rogation Wednesday'), ( 39, 'Ascension'), ( 49, 'Whitsunday (Pentecost)'), ( 56, 'Trinity Sunday'), ( 60, 'Corpus Christi') ) AS Category ( DaysFromEaster, Festival ) CROSS JOIN --easter day for each year ( SELECT datefromparts(Yr, [day] / 31, ( [day] % 31 ) + 1) AS Easter_Day FROM ( SELECT 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] FROM years ) g ) E UNION ALL SELECT datefromparts( yr, TheMonth, TheDay), Festival FROM -- these are easy to do and we all just use the DateFromParts function now ( VALUES ( 1, 1, 'The Circumcision of Christ'), ( 6, 1, 'Epiphany'), <span class="style1"> </span> ( 13, 1, 'Day of St Hilary the Bishop'), ( 25, 1, 'The Conversion of St Paul'), ( 2, 2, 'The Purification of the Blessed Virgin Mary and the Presentation of Christ in the Temple (Candlemas)'), ( 14, 2, 'St Valentine''s Day'), ( 24, 2, 'St Matthias'' Day'), ( 1, 3, 'St David''s Day'), ( 12, 3, 'St Gregory''s Day'), ( 17, 3, 'St Patrick''s Day'), ( 18, 3, 'The Day of St Edward, King of the West Saxons'), ( 25, 3, 'Annunciation of the Virgin (Lady Day or Ladymas)'), ( 4, 4, 'St Ambrose''s Day'), ( 23, 4, 'St George''s Day'), ( 25, 4, 'St Mark''s day'), ( 1, 5, 'The Day of St Philip and St James the Less.'), ( 26, 5, 'The Day of St Augustine, first Archbishop of Canterbury'), ( 24, 6, 'The Nativity of St John Baptist'), ( 29, 6, 'The Day of St Peter and St Paul'), ( 2, 7, 'The Visitation of the Blessed Virgin Mary'), ( 22, 7, 'St Mary Magdalene''s Day'), ( 25, 7, 'The Day of St James the Apostle'), ( 6, 8, 'The Transfiguration'), ( 10, 8, 'St Lawrence''s day'), ( 13, 8, 'Lammas Day (from 1753- 1st August before then)'), ( 24, 8, 'The Day of St Bartholomew the Apostle'), ( 8, 9, 'The Nativity of the Virgin Mary'), ( 14, 9, 'Holy Cross Day (Holy Rood Day or Roodmas)'), ( 21, 9, 'St Matthew the Apostle'), ( 29, 9, 'St Michael and All Angels (Michaelmas)'), ( 30, 9, 'St Jerome''s day'), ( 18, 10, 'St Luke''s day'), ( 25, 10, 'St Crispin''s day'), ( 28, 10,'St Simon and St Jude the Apostles'), ( 1, 11, 'All Saints (Hallowmas or AllHallows)'), ( 2, 11,'All Souls'' Day'), ( 11, 11, 'St Martin''s day (Martinmas)'), ( 22, 11, 'St Cecilia''s day'), ( 30, 11,'St Andrew''s day'), ( 6, 12, 'St Nicholas''s day'), ( 8, 12,'The Conception of the Blessed Virgin Mary'), ( 21, 12, 'The Day of St Thomas the Apostle'), ( 25, 12,'Christmas Day'), ( 26, 12, 'The Feast of St Stephen'), ( 27, 12, 'The Day of St John the Evangelist'), ( 28, 12,'Holy Innocents Day (Childermas)'), ( 29, 12, 'The Day of St Thomas a Becket') ) f ( TheDay, TheMonth, Festival ) CROSS JOIN Years UNION ALL SELECT /* Advent sunday is the sunday nearest to St Andrews' Day*/ dateadd (DAY, CASE WHEN Daydifference < 4 THEN -DayDifference ELSE 7 - Daydifference END, StAndrewsDay), 'Advent' FROM (SELECT datediff(DAY, '01 Jan 1899', datefromparts( Yr, 11, 30)) % 7 AS DayDifference, datefromparts( Yr, 11, 30) AS StAndrewsDay from Years )f ORDER BY TheDate |
This gives (just for the one year, in which I wrote it)
- 2015-01-01 The Circumcision of Christ
- 2015-01-06 Epiphany
- 2015-01-13 Day of St Hilary the Bishop
- 2015-01-25 The Conversion of St Paul
- 2015-02-01 Septuagesima Sunday
- 2015-02-02 The Purification of the Blessed Virgin Mary etc.
- 2015-02-08 Sexagesima Sunday
- 2015-02-14 St Valentine’s Day
- 2015-02-15 Quinquagesima Sunday
- 2015-02-17 Shrove Tuesday or Mardi Gras
- 2015-02-18 Ash Wednesday
- 2015-02-24 St Matthias’ Day
- 2015-03-01 St David’s Day
- 2015-03-12 St Gregory’s Day
- 2015-03-17 St Patrick’s Day
- 2015-03-18 The Day of St Edward, King of the West Saxons
- 2015-03-25 Annunciation of the Virgin (Lady Day or Ladymas)
- 2015-03-29 Palm Sunday
- 2015-04-02 Maundy Thursday
- 2015-04-03 Good Friday
- 2015-04-04 St Ambrose’s Day
- 2015-04-05 Easter sunday
- 2015-04-23 St George’s Day
- 2015-04-25 St Mark’s day
- 2015-05-01 The Day of St Philip and St James the Less.
- 2015-05-10 Rogation
- 2015-05-11 Rogation Monday
- 2015-05-12 Rogation Tuesday
- 2015-05-13 Rogation Wednesday
- 2015-05-14 Ascension
- 2015-05-24 Whitsunday (Pentecost)
- 2015-05-24 Pentecost
- 2015-05-26 The Day of St Augustine, first Archbishop of Canterbury
- 2015-05-31 Trinity Sunday
- 2015-06-04 Corpus Christi
- 2015-06-24 The Nativity of St John Baptist
- 2015-06-29 The Day of St Peter and St Paul
- 2015-07-02 The Visitation of the Blessed Virgin Mary
- 2015-07-22 St Mary Magdalene’s Day
- 2015-07-25 The Day of St James the Apostle
- 2015-08-06 The Transfiguration
- 2015-08-10 St Lawrence’s day
- 2015-08-13 Lammas Day (from 1753- 1st August before then)
- 2015-08-24 The Day of St Bartholomew the Apostle
- 2015-09-08 The Nativity of the Virgin Mary
- 2015-09-14 Holy Cross Day (Holy Rood Day or Roodmas)
- 2015-09-21 St Matthew the Apostle
- 2015-09-29 St Michael and All Angels (Michaelmas)
- 2015-09-30 St Jerome’s day
- 2015-10-18 St Luke’s day
- 2015-10-25 St Crispin’s day
- 2015-10-28 St Simon and St Jude the Apostles
- 2015-11-01 All Saints (Hallowmas or AllHallows)
- 2015-11-02 All Souls’ Day
- 2015-11-11 St Martin’s day (Martinmas)
- 2015-11-22 St Cecilia’s day
- 2015-11-29 Advent
- 2015-11-30 St Andrew’s day
- 2015-12-06 St Nicholas’s day
- 2015-12-08 The Conception of the Blessed Virgin Mary
- 2015-12-21 The Day of St Thomas the Apostle
- 2015-12-25 Christmas Day
- 2015-12-26 The Feast of St Stephen
- 2015-12-27 The Day of St John the Evangelist
- 2015-12-28 Holy Innocents Day (Childermas)
- 2015-12-29 The Day of St Thomas a Becket
So there you have it. Plenty of potential feasts to celebrate there!
Load comments