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)