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.