### 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] GOCREATE 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)`

ċ
Andy Hughes,
Jun 21, 2012, 10:55 AM
Comments