# Calculating Easter in SQL

• Comments posted to this topic are about the item Calculating Easter in SQL

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• Very nice collection of algorithms, Jeffrey.  I've got one more for you to add to your collection.

Peter "Peso" Larsson came up with the following basic method quite a while back.  Apparently, he pre-calculated the dates for the 19 year cycle of the moon and calculates the following Sunday.  I've not verified the method all the way out to 9999 but have verified it for the previous, current, and next centuries.

The first link below is where Peter originally published it and the second link is a "near enough" explanation although it uses a couple of tables to calculate the base date and the offsets instead of the simple integer math and "find a Sunday" date trick the we've all grown to know and love.  Here's Peter's code with a little "Modenization" to reduce the clock cycles a bit and turn it into an iSF (iTVF that returns a scalar value).

`--REF: https://weblogs.sqlteam.com/peterl/2010/09/08/fast-easter-day-function/--REF: https://www.assa.org.au/edm#Index CREATE OR ALTER FUNCTION dbo.GetEasterDate        (@Year CHAR(4))RETURNS TABLE AS RETURN SELECT EasterDate = DATEADD(DAY,DATEDIFF(DAY,0,@Year+v.BaseDate)/7*7,6) --Finds Sunday after BaseDate   FROM (VALUES         ( 0,'0415')        ,( 1,'0404')        ,( 2,'0324')        ,( 3,'0412')        ,( 4,'0401')        ,( 5,'0419')        ,( 6,'0409')        ,( 7,'0329')        ,( 8,'0417')        ,( 9,'0406')        ,(10,'0326')        ,(11,'0414')        ,(12,'0403')        ,(13,'0323')        ,(14,'0411')        ,(15,'0331')        ,(16,'0418')        ,(17,'0408')        ,(18,'0328')        ) v (Cycle,BaseDate)  WHERE @Year BETWEEN '1900' AND '9999'    AND v.Cycle = @Year%19;`

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Many decades ago, I had to set up a calendar table (okay, in those days it was a file) for an enterprise that gave Easter holidays. I had to subordinates and I asked them to go to their churches to get the official church calendars for Easter. Yes, being a math major, I had the algorithms but I wanted to see what was actually being done by the church. I didn't realize that my two subordinates were Greek and Russian Orthodox. They did exactly what I asked him to do and gave us a 20-year list of Easter's. Too bad we were Catholic as an enterprise. I did a little spot check and at that time the Orthodox and the Catholic Easter dates coincided.

• Thanks, Joe.  I was reading about that in the second link I provided.  Interesting bit of history that still affects us today.  So, to answer the question of "When is Easter?", like all else in SQL Server, the correct answer is "It Depends". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I included 3 separate algorithms for calculating Orthodox Easter in the script.  Those algorithms default to providing the Julian date prior to 1783 which is when Britain and her colonies adopted the Gregorian calendar.  Some areas adopted the Gregorian calendar in 1582 - others did not adopt until 1922 (Greece).

Orthodox churches still utilize the Julian calendar for Easter - even if they are using the Gregorian calendar, which is why we find that those areas celebrate Easter on a different date.  For those areas it would be a simple change in the script to provide the correct date based on when the Gregorian calendar was adopted.

I utilized the information from Computus here: https://en.wikipedia.org/wiki/Computus.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• I saw those in your code.  Like I said, well done on the article.  I've not seen such a collection of methods for calculating what Easter Day is all in one place for SQL before.  Well done, again, Jeffrey!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

Very nice collection of algorithms, Jeffrey.  I've got one more for you to add to your collection.

Peter "Peso" Larsson came up with the following basic method quite a while back.  Apparently, he pre-calculated the dates for the 19 year cycle of the moon and calculates the following Sunday.  I've not verified the method all the way out to 9999 but have verified it for the previous, current, and next centuries.

The first link below is where Peter originally published it and the second link is a "near enough" explanation although it uses a couple of tables to calculate the base date and the offsets instead of the simple integer math and "find a Sunday" date trick the we've all grown to know and love.  Here's Peter's code with a little "Modenization" to reduce the clock cycles a bit and turn it into an iSF (iTVF that returns a scalar value).

`--REF: https://weblogs.sqlteam.com/peterl/2010/09/08/fast-easter-day-function/--REF: https://www.assa.org.au/edm#Index CREATE OR ALTER FUNCTION dbo.GetEasterDate        (@Year CHAR(4))RETURNS TABLE AS RETURN SELECT EasterDate = DATEADD(DAY,DATEDIFF(DAY,0,@Year+v.BaseDate)/7*7,6) --Finds Sunday after BaseDate   FROM (VALUES         ( 0,'0415')        ,( 1,'0404')        ,( 2,'0324')        ,( 3,'0412')        ,( 4,'0401')        ,( 5,'0419')        ,( 6,'0409')        ,( 7,'0329')        ,( 8,'0417')        ,( 9,'0406')        ,(10,'0326')        ,(11,'0414')        ,(12,'0403')        ,(13,'0323')        ,(14,'0411')        ,(15,'0331')        ,(16,'0418')        ,(17,'0408')        ,(18,'0328')        ) v (Cycle,BaseDate)  WHERE @Year BETWEEN '1900' AND '9999'    AND v.Cycle = @Year%19;`

I was aware of Peter's solution and reviewed it when I was developing this script.  I did not utilize this methodology because it was limited to 1900 and forward - and did not have a corresponding Orthodox version.

I only included the New Scientist algorithm because it is a modification of the original Anonymous algorithm and shows a slightly different approach.

I reviewed quite a few different approaches and there are several other algorithms.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

Viewing 7 posts - 1 through 7 (of 7 total)