Calculating Easter in SQL

Question

Calculating Easter in SQL

Jeffrey Williams

SSC Guru

Points: 90252
More actions
March 31, 2020 at 10:46 pm

#3739463

Comments posted to this topic are about the item Calculating Easter in SQL
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 25, 2023 at 3:44 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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.

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

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 2

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.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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.

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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.

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 6

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 7

The function returns the correct date between year 1900 and 2203.

N 56°04'39.16"
E 12°55'05.25"