Calculating Easter in SQL
Several months ago someone commented on a calendar thread that you should not even try to calculate Easter in SQL Server because the math was too hard. That got me to thinking and I decided to see if that was actually the case.
I found that there are several well known algorithms available - but could they be converted into an efficient SQL solution? Turns out - it really isn't that hard. The attached script includes 7 different algorithms to calculate Easter - 4 Western algorithms and 3 Eastern (Orthodox) algorithms.
The following algorithms are included:
- Gauss' Easter algorithm
- Gauss' Julian algorithm (Orthodox "Eastern" Easter)
- Anonymous Gregorian 'Meeus/Jones/Butcher' algorithm
- Meeus's Julian algorithm (Orthodox "Eastern" Easter)
- Oudin's Easter algorithm
- Oudin's Julian algorithm (Orthodox 'Eastern' Easter)
- New Scientist (30 March 1961) - Modification of the Anonymous Gregorian algorithm
I built this script to show the results from each algorithm and all variables used to calculate Easter. You can remove the '*' from each algorithm to return just the dates for easier comparison and if you uncomment the final cross apply and select only the columns from the final cross apply you can test how long it takes to generate all related dates.
Set Nocount On;
Set STATISTICS time On;
Declare @startYear bigint = 0001
, @endYear bigint = 9999;
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, inputYears (y)
As (
Select Top (@endYear - @startYear + 1)
(@startYear - 1) + row_number() over(Order By @@spid) As rn
From t t1, t t2, t t3, t t4
)
Select *
From inputYears dd
/* Gauss' Easter algorithm
a y % 19 year's position in the 19-year lunar phase cycle
b,c,k y % 4, y % 7, y / 100 corrections for century years
p (13 + 8*k) / 25 correct for the lunar orbit not being fully describable in integer terms
q k / 4 leap-year exceptions in century years
M (15 - p + k - q) % 30 correct starting point at the start of each century
N (4 + k - q) % 7 starting point for each century
d (19*a + M) % 30 number of days between 21 March and the coincident or next following full moon
e (2*b + 4*c + 6*d + N) % 7) offset days that must be added to make d arrive on a Sunday
o iif(e = 6 And (d = 29 Or (d = 28 And a > 10)), 7, 0) subtract days for 26 April OR 25 April AND a > 10
Add d + e - o to March 22 Easter Sunday (Gregorian calendar)
*/
Cross Apply (
Select * --e7.EasterDay
From (Values (y % 19, y % 4, y % 7, y / 100)) As e1(a, b, c, k)
Cross Apply (Values ((13 + 8*k) / 25, k / 4)) As e2(p, q)
Cross Apply (Values ((15 - p + k - q) % 30, (4 + k - q) % 7)) As e3(M, N)
Cross Apply (Values ((19*a + M) % 30)) As e4(d)
Cross Apply (Values ((2*b + 4*c + 6*d + N) % 7)) As e5(e)
Cross Apply (Values (iif(e = 6 And (d = 29 Or (d = 28 And a > 10)), 7, 0))) As e6(o)
Cross Apply (Values (dateadd(day, d + e - o, datefromparts(y, 3, 22)))) As e7(EasterDay)
) As ge
/* Gauss' Julian algorithm (Orthodox "Eastern" Easter Day)
a y % 19 year's position in the 19-year lunar phase cycle
b, c y % 4, y % 7 leap-year dates
d (19*a + 15) % 30 Paschal Full Moon for this year
e (2*a + 4*b + 6*d + 6) % 7 the Sunday following the Paschal Full Moon
j (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar
Add d + e to March 22 Julian Easter Date
Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar)
*/
Cross Apply (
Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate)
From (Values (y % 19, y % 4, y % 7)) As e1(a, b, c)
Cross Apply (Values ((19*a + 15) % 30)) As e2(d)
Cross Apply (Values ((2*b + 4*c + 6*d + 6) % 7, (y / 100 - y / 400) - 2)) As e3(e, o)
Cross Apply (Values (dateadd(day, d + e, datefromparts(y, 3, 22)))) As e4(JulianDate)
Cross Apply (Values (dateadd(day, o, JulianDate))) As e5(GregorianDate)
) As oge
/* Anonymous Gregorian 'Meeus/Jones/Butcher' algorithm (Western Easter Day)
a y % 19 year's position in the 19-year lunar phase cycle
b, c y / 100, y % 100 corrections for century years
d, e b / 4, b % 4 leap-year exceptions in century years
f, g (b + 8) / 25, (b - f + 1) / 3 century-year auxiliary corrections to the new-moon and full-moon dates
h (19*a + b - d - g + 15) % 30 number of days between 21 March and the coincident or next following full moon
i, k c / 4, c % 4 position of the year in the ordinary leap-year cycle
l (32 + 2*e + 2*i - h - k) % 7 number between 0 and 6 which is one less than the number of days before the next
Sunday which is definitely after the full moon
m (a + 11*h + 22*l) / 451 correction for transfer of full moon from a Sunday to a Saturday
m = 1 for the required correction else 0
x h + l - 7*m + 114 h + l - 7*m = number of days between 21 March and Easter
x / 31, (x % 31) + 1 the month and day on the Gregorian calendar
*/
Cross Apply (
Select * --e8.EasterDay
From (Values (y % 19, y / 100, y % 100)) As e1(a, b, c)
Cross Apply (Values (b / 4, b % 4, (b + 8) / 25)) As e2(d, e, f)
Cross Apply (Values ((b - f + 1) / 3)) As e3(g)
Cross Apply (Values ((19*a + b - d - g + 15) % 30, c / 4, c % 4)) As e4(h, i, k)
Cross Apply (Values ((32 + 2*e + 2*i - h - k) % 7)) As e5(l)
Cross Apply (Values ((a + 11*h + 22*l) / 451)) As e6(m)
Cross Apply (Values (h + l - 7*m + 114)) As e7(x)
Cross Apply (Values (datefromparts(y, x / 31, (x % 31) + 1))) As e8(EasterDay)
) As me
/* Meeus's Julian algorithm (Orthodox "Eastern" Easter Day)
a, b y % 4, y % 7 leap-year dates
c y % 19 year's position in the 19-year lunar phase cycle
d (19*c + 15) % 30 Paschal Full Moon for this year
e (2*a + 4*b - d + 34) % 7 the Sunday following the Paschal Full Moon
o (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar
n (d + e + 114) / 31 the month on the Julian Calendar
p (d + e + 114) % 31) + 1 the day of the month on the Julian Calendar
Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar)
*/
Cross Apply (
Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate)
From (Values (y % 4, y % 7, y % 19)) As e1(a, b, c)
Cross Apply (Values ((19*c + 15) % 30)) As e2(d)
Cross Apply (Values ((2*a + 4*b - d + 34) % 7, (y / 100 - y / 400) - 2)) As e3(e, o)
Cross Apply (Values ((d + e + 114) / 31, ((d + e + 114) % 31) + 1)) As e4(n, p)
Cross Apply (Values (datefromparts(y, n, p))) As e5(JulianDate)
Cross Apply (Values (dateadd(day, o, JulianDate))) As e6(GregorianDate)
) As ome
/* Oudin's Easter algorithm
g y % 19 year's position in the 19-year lunar phase cycle
c y / 100 leap-year corrections for centuries
h (c - (c / 4) - ((13 + 8*c) / 25) + 19*g + 15) % 30 the number of days short to the full moon
i h - (h / 28) * (1 - (29 / (h + 1)) * ((21 - g) / 11)) number of days from 21 March to the Paschal full moon
j (y + (y / 4 + i + 2 - (c - (c / 4)))) % 7 the weekday for the Paschal full moon (0 = Sunday, 1 = Monday, etc.)
m 3 + (((i - j) + 40) / 44) the month on the Gregorian calendar
n (i - j) + 28 - 31 * (m / 4) the day of the month on the Gregorian calendar
*/
Cross Apply (
Select * --e7.EasterDay
From (Values (y % 19, y / 100)) As e1(g, c)
Cross Apply (Values ((c - (c / 4) - ((13 + 8*c) / 25) + 19*g + 15) % 30)) As e2(h)
Cross Apply (Values (h - (h / 28) * (1 - (29 / (h + 1)) * ((21 - g) / 11)))) As e3(i)
Cross Apply (Values ((y + (y / 4 + i + 2 - (c - (c / 4)))) % 7)) As e4(j)
Cross Apply (Values (3 + (((i - j) + 40) / 44))) As e5(m)
Cross Apply (Values ((i - j) + 28 - 31 * (m / 4))) As e6(n)
Cross Apply (Values (datefromparts(y, m, n))) As e7(EasterDay)
) As oe
/* Oudin's Julian algorithm (Orthodox 'Eastern' Easter)
g y % 19 year's position in the 19-year lunar phase cycle
c y / 100 leap-year corrections for centuries
i (19*g + 15) % 30) number of days from 21 March to the Paschal full moon
j (y + (y / 4) + i) % 7 the weekday for the Paschal full moon (0 = Sunday, 1 = Monday, etc.)
m 3 + (((i - j) + 40) / 44) the month on the Julian Calendar
n (i - j) + 28 - 31 * (m / 4) the day of the month on the Julian Calendar
o (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar
Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar)
*/
Cross Apply (
Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate)
From (Values (y % 19, y / 100)) As e1(g, c)
Cross Apply (Values ((19*g + 15) % 30)) As e2(i)
Cross Apply (Values ((y + (y / 4) + i) % 7)) As e3(j)
Cross Apply (Values (3 + (((i - j) + 40) / 44))) As e4(m)
Cross Apply (Values ((i - j) + 28 - 31 * (m / 4), (y / 100 - y / 400) - 2)) As e5(n, o)
Cross Apply (Values (datefromparts(y, m, n))) As e6(JulianDate)
Cross Apply (Values (dateadd(day, o, JulianDate))) As e7(GregorianDate)
) As ooe
/* New Scientist (30 March 1961) - Modification of the Anonymous Gregorian algorithm
a y % 19 year's position in the 19-year lunar phase cycle
b, c y / 100, y % 100 corrections for century years
d, e b / 4, b % 4 leap-year exceptions in century years
g 8 * b + 13 / 25 century-year auxiliary corrections to the new-moon and full-moon dates
h (19*a + b - d - g + 15) % 30 number of days between 21 March and the coincident or next following full moon
i, k c / 4, c % 4 position of the year in the ordinary leap-year cycle
l (32 + 2*e + 2*i - h - k) % 7 number between 0 and 6 which is one less than the number of days before the next
Sunday which is definitely after the full moon
m (a + 11*h + 19*l) / 433 correction for transfer of full moon from a Sunday to a Saturday
m = 1 for the required correction else 0
n (h + l - 7*m + 90) / 25 the month on the Gregorian Calendar
h + l - 7*m = number of days between 21 March and Easter
p (h + l - 7*m + 33*n + 19) % 32) the day of the month on the Gregorian calendar
*/
Cross Apply (
Select * --e9.EasterDay
From (Values (y % 19, y / 100, y % 100)) As e1(a, b, c)
Cross Apply (Values (b / 4, b % 4)) As e2(d, e)
Cross Apply (Values ((8*b + 13) / 25)) As e3(g)
Cross Apply (Values ((19*a + b - d - g + 15) % 30, c / 4, c % 4)) As e4(h, i, k)
Cross Apply (Values ((32 + 2*e + 2*i - h - k) % 7)) As e5(l)
Cross Apply (Values ((a + 11*h + 19*l) / 433)) As e6(m)
Cross Apply (Values ((h + l - 7*m + 90) / 25)) As e7(n)
Cross Apply (Values ((h + l - 7*m + 33*n + 19) % 32)) As e8(p)
Cross Apply (Values (datefromparts(y, n, p))) As e9(EasterDay)
) As ne
--==== Related Easter Holidays (using Gauss' Western algorithm)
--Cross Apply (
-- Select h.IsHoliday
-- , h.HolidayDate
-- , h.HolidayName
-- From (Values (ge.EasterDay)) As e(EasterDate)
-- Cross Apply (Values (0, dateadd(day,-46, e.EasterDate), 'Ash Wednesday')
-- , (0, dateadd(day, -7, e.EasterDate), 'Palm Sunday')
-- , (0, dateadd(day, -3, e.EasterDate), 'Maundy Thursday')
-- , (1, dateadd(day, -2, e.EasterDate), 'Good Friday')
-- , (0, dateadd(day, -1, e.EasterDate), 'Holy Saturday')
-- , (1, dateadd(day, 0, e.EasterDate), 'Easter Sunday')
-- , (0, dateadd(day, 39, e.EasterDate), 'Ascension Day')
-- , (0, dateadd(day, 49, e.EasterDate), 'Pentecost')
-- ) As h(IsHoliday, HolidayDate, HolidayName)
-- ) As hd;
Set STATISTICS time Off;
