Date and Time - Calculate Easter Sunday Date

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        Sometime over the last 2000 years
 

Description

Most public holiday dates fall on dates/days that can be calculated with simple absolute (Valentines Day = 14 Feb)  or offset (Victoria Day = 3rd Monday in May) rules.
Easter Sunday calculation follows a more complex set of rules called Computus, a definition of which can be found on Wikipedia.
 
The algorithm for calculating this has been ported through many systems, and the TSQL code below is a representation suitable for SQL Server.
 

Code

 

DROP FUNCTION [dbo].[udf_EasterSundayByYear]
GO

CREATE FUNCTION [dbo].[udf_EasterSundayByYear]
(@Year CHAR(4))
RETURNS SMALLDATETIME
AS
BEGIN
--http://aa.usno.navy.mil/faq/docs/easter.php
  
DECLARE
      
@c INT
      
, @n INT
      
, @k INT
      
, @i INT
      
, @j INT
      
, @l INT
      
, @m INT
      
, @d INT
      
, @Easter DATETIME

   SET
@c = (@Year / 100)
  
SET @n = @Year - 19 * (@Year / 19)
  
SET @k = (@c - 17) / 25
  
SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15
  
SET @i = @i - 30 * ( @i / 30 )
  
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
  
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
  
SET @j = @j - 7 * (@j / 7)
  
SET @l = @i - @j
  
SET @m = 3 + (@l + 40) / 44
  
SET @d = @l + 28 - 31 * ( @m / 4 )

  
SET @Easter = (SELECT RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '/' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + '/' + CONVERT(CHAR(4),@Year))

  
RETURN @Easter
END

--SELECT dbo.[udf_EasterSundayByYear] (2010)
--SELECT dbo.[udf_EasterSundayByYear] (2011)


ċ
Calculate date of Easter Sunday.sql
(1k)
Andy Hughes,
Jun 21, 2012, 10:55 AM
Comments