Phil Factor's Phrenetic Phoughts

Simple-Talk columnist
The wilder shores of Transact SQL    Phil on Twitter   Phil on SQL Server Central  Phil on BOS

Calculating Easter: The longest scientific Project ever?

Published Sunday, January 18, 2009 4:37 PM

On Friday, I'd managed to work myself into a rage about something. I then sat down and wrote the following function in TSQL that tells you the date of Easter for any year you wish. Afterwards, I felt sublimely at peace with the world. Perhaps I remain an unreconstructed geek, after all.

This is one bit of code that that I shall not attempt to document. I'll explain why afterwards.

ALTER FUNCTION Easter ( @input_date DATETIME )
/*
calculates the date of easter for the given year. This calculation
is the current one approved by the vatican. It differs from
the greek orthodox.

e.g.
DECLARE @Easter TABLE
    (
      year INT,
      Easter DATETIME
    )
DECLARE @TheYear DATETIME
SELECT  @TheYear = DATEADD(year, -15, CURRENT_TIMESTAMP)
WHILE DATEDIFF(year, @TheYear, '1 Jun 2020') > 0
    BEGIN
        INSERT  INTO @Easter ( Year, Easter )
                SELECT  DATEPART(year, @TheYear),
                        dbo.easter(@TheYear)
        SELECT  @TheYear = DATEADD(year, 1, @TheYear)
    END
SELECT  year,
        CONVERT(CHAR(11), Easter, 113) AS [Easter Day]
FROM    @easter    
*/
RETURNS DATETIME
    WITH EXECUTE AS
CALLER
AS BEGIN
    DECLARE
@y INTEGER,
        
@dy INTEGER,
        
@easter VARCHAR(20),
        
@easter_month INTEGER,
        
@easter_day INTEGER ;

    
SET @y = DATEPART(YEAR, @input_date) ;

    
SET @dy = ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) + ( ( 32 + 2 * ( ( @y / 100 ) % 4 ) + 2 * ( ( @y % 100 ) / 4 ) - ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) - 7 * ( ( ( @y % 19 ) + 11 * ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) + 22 * ( ( 32 + 2 * ( ( @y / 100 ) % 4 ) + 2 * ( ( @y % 100 ) / 4 ) - ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) ) / 451 ) + 114 ;

    
SET @easter_month = @dy / 31 ;
    
SET @easter_day = ( @dy % 31 ) + 1 ;

-- assumes proprietary, non-ANSI local temporal format
    
SET @easter = CASE @easter_month
                    
WHEN 3 THEN 'Mar'
                    
ELSE 'Apr'
                  
END ;
    
SET @easter = @easter + SPACE(1) + CAST(@easter_day AS VARCHAR(2)) + ', '
        
+ CAST(@y AS VARCHAR(4))
    
RETURN CAST(@easter AS DATETIME) ;
    
  
END ;

The story of the date of Easter is tinged with farce. By the third century, Christians had settled down to the idea of celebrating the anniversary of Jesus' resurrection. Unfortunately, nobody at the time had thought of jotting down the date when it happened. They felt sure that it had happened some time in the Jewish month of Nisan, at around the full moon. The Jewish calendar was lunar, and Christians were forced to rely on the Jews to tell them when the month  approached. Even then, calculating the Sunday after the full moon was almost impossible then, so the date chosen varied between Christian communities. At the council of Nicaea, in 325 AD,  Constantine determined that all Christians should celebrate on the same day, and they decided that Easter should be on the first Sunday after the first full moon after the spring equinox, but not if it occurred on the same day as the Jewish Passover. Constantine wasn't a 'small detail' man and optimistically concluded "we ought not to have anything with the (Jewish calendar), for the Saviour has shown us another way". He hadn't. They should have chosen a fixed date.

At this stage, I can imagine the astronomers, the geeks of the day, weeping, for much the same reasons as we weep now when managers promise the unattainable from IT. Even the fixed date would have been a compromise:  Caesar's calendar was flawed, any fixed date for the vernal equinox drifted by 11 minutes a year. Calculating the date of the full moon required a precise calculation of the sun, orbit of the earth and phases of the moon. and had to take into account the drift of the calendar.  It also had to compensate for the elliptical orbits of the planets, and the various gravitational effects. They couldn't do the calculation accurately then, and the struggle over the centuries to get the right answer  funded the dim flickering light of science, even at the darkest of times. In the nineteenth century, a millennium and a half later, Vatican scholars finally produced a complex fourteen-part algorithm to calculate the date, and it is still in use today. I nominate this as the longest, and most expensive, IT project ever.

The formula used gets the right date, but it is difficult to comprehend, and seems flawed to me. Nevertheless, it is the way Easter is calculated, so there is no sense in correcting it, unless you wish to start a new Christian sect. Meanwhile, the Greek Orthodox church have a different set of calculations for calculating Easter, but that is another story

Comments

 

mjswart said:

Also see someone else's attempts here:
http://www.mssqltips.com/tip.asp?tip=1537
(and comments for more attempts)
January 19, 2009 9:40 AM
 

StarNamer said:

I needed one a couple of years back to actually work out various public holidays, many of which are a number of days before/after Easter, hence the [MonthDayOffset] field. This works for years (@year) from 1900 to 2099...

dateadd(d,(case @year when 1954 then 14 when 1981 then 14 when 2049 then 14 when 2076 then 14 else 21 end
+ ((19 * (@year % 19) + 24 ) % 30) + ((2 * (@year % 4) + 4 * (@year % 7) + 6 * ((19 * (@year % 19) +24 ) % 30) + 5) % 7))
+[MonthDayOffset],convert(char(4),@year)+'0301')

I decided it was simpler to 'kludge' the special cases rather than find a more complex formula!
January 23, 2009 8:11 AM
 

woakesd said:

One slight improvement could be made: use the date function which takes three parameters (year, month and day) to return a datetime value.

I hate the use of strings to build dates and followed by the use of cast or convert, it is plain evil and shows sloppy programming.

Also there is non ansi translation of month number to string using this function.
January 27, 2009 8:25 AM
You need to sign in to comment on this blog



















<January 2009>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567
Minesweeper in T-SQL
 Whatever happened to the idea that programming in TSQL can be fun? A Simple-Talk reader contributes an... Read more...

SQL Source Control: The Development Story, Part II
 When creating SQL Source Control, the team had to make decisions as to which source control systems the... Read more...

Raw Materials: Healthy Caution or Something Else?
 Derek slips a cog. Read more...

The DIS-Information Principle, Part II
 Database design simply involves populating a schema with tables that model sets of entities and... Read more...

OCS Disaster Recovery, Part 2
 There are several possible disasters which might happen to your Office Communications Server... Read more...