Calculating the date of easter sunday

We’ve previously looked at how to calculate recurring public holidays. However, calculating the date of easter sunday is not as simple as you might think, because it involves calculations of lunar phases. This short post contains a T-SQL translation of the popularly used Meeus-Jones-Butcher formula.

According to swedish standards (and holiday legislation), easter sunday occurs on the first sunday following the first full moon on or after march 21. For the record, there are different easters – this post is about the western (i.e. Gregorian) easter sunday.

As I don’t know the first thing about astronomy, moon phases or religious holidays, I’ll cut straight to the chase. The formula calculates the number of days from january 1 for a specific year, using a number of variables. I’ve translated this into a scalar function that accepts @year as input and outputs the date of easter sunday for that year:

CREATE FUNCTION dbo.fn_easterSunday(@year smallint)
RETURNS date
WITH SCHEMABINDING
AS

--- Calculates the date of easter sunday for a given year, using
--- the Meeus-Jones-Butcher algorithm.
---
--- Source: http://en.wikipedia.org/wiki/Computus

BEGIN;

    --- Variables used:
    DECLARE @a tinyint, @b tinyint, @c tinyint,
            @d tinyint, @e tinyint, @f tinyint,
            @g tinyint, @h tinyint, @i tinyint,
            @k tinyint, @l tinyint, @m tinyint,
            @date date;

    --- Calculation steps:
    SELECT @a=@year%19, @b=FLOOR(1.0*@year/100), @c=@year%100;
    SELECT @d=FLOOR(1.0*@b/4), @e=@b%4, @f=FLOOR((8.0+@b)/25);
    SELECT @g=FLOOR((1.0+@b-@f)/3);
    SELECT @h=(19*@a+@b-@d-@g+15)%30, @i=FLOOR(1.0*@c/4), @k=@year%4;
    SELECT @l=(32.0+2*@e+2*@i-@h-@k)%7;
    SELECT @m=FLOOR((1.0*@a+11*@h+22*@l)/451);
    SELECT @date=
        DATEADD(dd, (@h+@l-7*@m+114)%31,
            DATEADD(mm, FLOOR((1.0*@h+@l-7*@m+114)/31)-1,
                DATEADD(yy, @year-2000, {d '2000-01-01'})
            )
        );

    --- Return the output date:
    RETURN @date;

END;

As far as I can establish, the formula seems to work in all fairly “contemporary” years, i.e. 1800s, 1900s and 2000s.

Check back next week, to see how you can use this formula as a building block to create a list of public holidays that you could use in, for instance, a date dimension.

One thought on “Calculating the date of easter sunday

  1. Pingback: Building a calendar dimension with public holidays « Sunday morning T-SQL

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s