Calculate Easter Sunday Dynamically using SQL

SQL Server / July 16, 2022 / , , , , ,
In any data warehousing or analytics solution, you will eventually need to generate a calendar table in order to perform time intelligence analytics.
 
One key aspect that most businesses will require is to view KPIs by working days only i.e. days when the business was operating normally. Each business will have their own unique rule sets as to what constitutes a work day, but there are a few standard rules that should apply to the vast majority (perhaps excluding retail).
 
Namely: Any day is a working day except
  • Weekends – Saturday & Sunday
  • Public Holidays

Weekends are pretty easy to identify, but public holidays not as much. Most public holidays are associated with fixed dates e.g. Christmas is always December 25th. But the most elusive one is Easter Sunday, because it does not occur on a fixed date each year.

In this post, I will demonstrate a technique to dynamically calculate the date of Easter Sunday so that you can allocate that public holiday dynamically on your calendar table in an SQL relational database system without the need for a manually curated list of public holidays.

Quick Maths

According to Wikipedia :
Easter is celebrated on the first Sunday after the Paschal full moon, which is the first full moon on or after 21 March (a fixed approximation of the March equinox). Determining this date in advance requires a correlation between the lunar months and the solar year, while also accounting for the month, date, and weekday of the Julian or Gregorian calendar.[4] The complexity of the algorithm arises because of the desire to associate the date of Easter with the date of the Jewish feast of Passover which, Christians believe, is when Jesus was crucified.
There are a few algorithms to calculate Easter Sunday taking the above into account. One such algorithm is the Meeus’s Julian algorithm.
 
Fellow SQL Nerd sqlsunday.com posted this article called “Calculating The Date Of Easter Sunday” on 2014-07-05.
He essentially took the Meeus’s Julian algorithm and SQL’ified it.

I then decided to alter his code to my own flavor of syntax to produce the below Scalar User Defined Function.

Scalar User Defined Function Version

CREATE OR ALTER FUNCTION [dbo].[ufn_CalcualteEasterSundayFromYear] 
(
    @pYear SMALLINT
)
RETURNS DATE
AS
/*=====================================================================================================================================================
Description:	Calculates the date of easter sunday for a given year, using the Meeus-Jones-Butcher algorithm.
                The formula calculates the number of days from january 1 for a specific year, using a number of variables.
Source: 		http://en.wikipedia.org/wiki/Computus
-------------------------------------------------------------------------------------------------------------------------------------------------------
Output Structure
    Scalar value with the following data type: DATE
-------------------------------------------------------------------------------------------------------------------------------------------------------
--Place code to test object here
SELECT [dbo].[ufn_CalcualteEasterSundayFromYear] (2022)
=====================================================================================================================================================*/
BEGIN
    /* Declare Variables used */
    DECLARE 
	    @varA TINYINT
        , @varB TINYINT
        , @varC TINYINT
        , @varD TINYINT
        , @varE TINYINT
        , @varF TINYINT
        , @varG TINYINT
        , @varH TINYINT
        , @varI TINYINT
        , @varK TINYINT
        , @varL TINYINT
        , @varM TINYINT
        , @varDate DATE;

    /* Calculation steps */
    SELECT 
	    @varA = @pYear % 19
        , @varB = FLOOR(1.0 * @pYear / 100)
        , @varC = @pYear % 100;

    SELECT 
	    @varD = FLOOR(1.0 * @varB / 4)
        , @varE = @varB % 4
        , @varF = FLOOR((8.0 + @varB) / 25);

    SELECT 
	    @varG = FLOOR((1.0 + @varB - @varF) / 3);

    SELECT 
	    @varH = (19 * @varA + @varB - @varD - @varG + 15) % 30
        , @varI = FLOOR(1.0 * @varC / 4)
        , @varK = @pYear % 4;

    SELECT 
        @varL = (32.0 + 2 * @varE + 2 * @varI - @varH - @varK) % 7;

    SELECT 
        @varM = FLOOR((1.0 * @varA + 11 * @varH + 22 * @varL) / 451);

    SELECT 
        @varDate = DATEADD(dd, (@varH + @varL - 7 * @varM + 114) % 31, DATEADD(mm, FLOOR((1.0 * @varH + @varL - 7 * @varM + 114) / 31) - 1, DATEADD(yy, @pYear - 2000, {d '2000-01-01' })));

    /* Return the output date*/
    RETURN @varDate;
END;
The code above should work in any SQL based system that supports user defined scalar functions e.g. Microsoft SQL Server.
 
However, there are some SQL systems out there that do not. Hence…

Inline Table Valued User Defined Function Version

This Inline Table Valued User Defined Function was purposefully designed because Azure Synapse Analytics Serverless does not support Scalar User Defined Function Versions (for some stupid reason, ugh).
 
Thus, I took the same logic that was used in the Scalar Function and made it return as a Inline Table Valued User Defined Function instead.
 
The only difference is in how you would call this function – see the example in the comment section at the top of the function definition.
CREATE OR ALTER FUNCTION [dbo].[ufn_tvf_CalcualteEasterSundayFromYear]
(
    @pYear SMALLINT
)
RETURNS TABLE
AS
/*=====================================================================================================================================================
Description:	Calculates the date of easter sunday for a given year, using the Meeus-Jones-Butcher algorithm.
Source: 		http://en.wikipedia.org/wiki/Computus
                The formula calculates the number of days from january 1 for a specific year, using a number of variables.
-------------------------------------------------------------------------------------------------------------------------------------------------------
Output Structure
    Table with the following schema
    [EasterSundayDate] DATE
-------------------------------------------------------------------------------------------------------------------------------------------------------
--Place code to test object here
SELECT *
FROM [dbo].[ufn_tvf_CalcualteEasterSundayFromYear] (2022)
=====================================================================================================================================================*/
RETURN

    WITH [CTE1]
    AS
    (
        SELECT 
            varA = @pYear % 19
            , varB = FLOOR(1.0 * @pYear / 100)
            , varC = @pYear % 100
    )
    , [CTE2]
    AS
    (
        SELECT 
            /*Previous step fields*/
            varA
            , varB
            , varC
            /*New fields*/
            , varD = FLOOR(1.0 * varB / 4)
            , varE = varB % 4
            , varF = FLOOR((8.0 + varB) / 25)
        FROM [CTE1]
    )
    , [CTE3]
    AS
    (
        SELECT 
            /*Previous step fields*/
            varA
            , varB
            , varC
            , varD
            , varE
            , varF
            /*New fields*/
            , varG = FLOOR((1.0 + varB - varF) / 3)
        FROM [CTE2]
    )
    , [CTE4]
    AS
    (
        SELECT             
            /*Previous step fields*/
            varA
            , varB
            , varC
            , varD
            , varE
            , varF
            , varG
            /*New fields*/
            , varH = (19 * varA + varB - varD - varG + 15) % 30
            , varI = FLOOR(1.0 * varC / 4)
            , varK = @pYear % 4
        FROM [CTE3]
    )
    , [CTE5]
    AS
    (
        SELECT
        /*Previous step fields*/
            varA
            , varB
            , varC
            , varD
            , varE
            , varF
            , varG
            , varH
            , varI
            , varK
            /*New fields*/
            , varL = (32.0 + 2 * varE + 2 * varI - varH - varK) % 7
        FROM [CTE4]
    )
    , [CTE6]
    AS
    (
        SELECT 
            /*Previous step fields*/
            varA
            , varB
            , varC
            , varD
            , varE
            , varF
            , varG
            , varH
            , varI
            , varK
            , varL
            /*New fields*/
            , varM = FLOOR((1.0 * varA + 11 * varH + 22 * varL) / 451)
        FROM [CTE5]
    )
    SELECT 
        [EasterSundayDate] = DATEADD(dd, (varH + varL - 7 * varM + 114) % 31, DATEADD(mm, FLOOR((1.0 * varH + varL - 7 * varM + 114) / 31) - 1, DATEADD(yy, @pYear - 2000, {d '2000-01-01' })))
    FROM[CTE6]

On the next episode of Dragonball Z

These functions are nifty, but need to be incorporated into larger Calendar generation procedures.
 
Look out for future posts that show just how to do that. 

If you like what I do please consider supporting me on Ko-Fi

Related Posts

5 thoughts on “Calculate Easter Sunday Dynamically using SQL”

  1. houston junk car buyer

    I am really loving the theme/design of your web site.

    Do you ever run into any internet browser compatibility problems?
    A small number of my blog readers have complained about my blog not working correctly in Explorer but looks great
    in Chrome. Do you have any ideas to help fix this issue?

  3. Micheal Chatampaya

    Pretty great post. I just stumbled upon your weblog and wished to mention that I have truly loved surfing around your weblog posts. After all I’ll be subscribing for your feed and I hope you write once more soon!

Comments are closed.