Calculating Easter in SQL

  • Jeffrey Williams

    SSC Guru

    Points: 88587

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

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeff Moden

    SSC Guru

    Points: 996809

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    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: 996809

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88587

    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.

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

  • Jeff Moden

    SSC Guru

    Points: 996809

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88587

    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.

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

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

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