Technical Article

Calculate gregorian easter sunday using a table valued function

,

This function could be used as a cross apply join to get the yearly gregorian easter sunday.

CREATE FUNCTION [dbo].[ufn_inltab_getGregorianEasterSunday](@year SMALLINT)
    RETURNS TABLE
    RETURN
--  http://www.robinlangell.com/calculate-gregorian-easter-sunday-fast-with-a-table-valued-function-in-tsql/
    WITH
    Step1 AS( SELECT a = @year%19, b = FLOOR(1.0*@year/100), c = @year%100 ),
    Step2 AS( SELECT a,b,c,d = FLOOR(1.0*b/4), e=b%4, f=FLOOR((8.0+b)/25) FROM Step1 ),
    Step3 AS( SELECT a,b,c,d,e,g = FLOOR((1.0+ b- f)/3) FROM Step2 ),
    Step4 AS( SELECT a,d,e,h = (19*a+b-d-g+15)%30, i= FLOOR(1.0*c/4), k= @year%4 FROM Step3 ),
    Step5 AS( SELECT a,h,l = (32.0+2*e+2*i-h-k)%7 FROM Step4 ),
    Step6 AS( SELECT h,l,m = FLOOR((1.0*a+11*h+22*l)/451) FROM Step5 ),
    Step7 AS( SELECT easterSunday =
    CAST( DATEADD(dd, (h+l-7*m+114)%31,
    DATEADD(mm, FLOOR((1.0*h+l-7*m+114)/31)-1,
    DATEADD(yy, @year-2000, '2000-01-01') )
    ) AS DATE)
    FROM Step6
    )
    SELECT easterSunday
    FROM Step7

    GO

    CREATE FUNCTION [dbo].[ufn_inltab_findGregorianEasterSunday](@date date)
    RETURNS TABLE
    RETURN
--  http://www.robinlangell.com/calculate-gregorian-easter-sunday-fast-with-a-table-valued-function-in-tsql/
    WITH
    Step1 AS( SELECT a = YEAR(@date)%19, b = FLOOR(1.0*YEAR(@date)/100), c = YEAR(@date)%100 ),
    Step2 AS( SELECT a,b,c,d = FLOOR(1.0*b/4), e=b%4, f=FLOOR((8.0+b)/25) FROM Step1 ),
    Step3 AS( SELECT a,b,c,d,e,g = FLOOR((1.0+ b- f)/3) FROM Step2 ),
    Step4 AS( SELECT a,d,e,h = (19*a+b-d-g+15)%30, i= FLOOR(1.0*c/4), k= YEAR(@date)%4 FROM Step3 ),
    Step5 AS( SELECT a,h,l = (32.0+2*e+2*i-h-k)%7 FROM Step4 ),
    Step6 AS( SELECT h,l,m = FLOOR((1.0*a+11*h+22*l)/451) FROM Step5 ),
    Step7 AS( SELECT easterSunday =
    CAST( DATEADD(dd, (h+l-7*m+114)%31,
    DATEADD(mm, FLOOR((1.0*h+l-7*m+114)/31)-1,
    DATEADD(yy, YEAR(@date)-2000, '2000-01-01') )
    ) AS DATE)
    FROM Step6
    ),
    Step8 AS ( SELECT isEasterSunday = CASE WHEN easterSunday = @date THEN 1 ELSE 0 END FROM Step7 )
    SELECT isEasterSunday
    FROM Step8

    GO

    -- ================================== Year test ============================ --
    -- N-table on the fly:
    WITH c1(N) AS ( SELECT N FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS N(N) )
    ,c2(N) AS (SELECT a.N FROM c1 a CROSS APPLY c1 b)
    ,c3(N) AS (SELECT a.N FROM c2 a CROSS APPLY c2 b)
    ,c4(N) AS (SELECT a.N FROM c3 a CROSS APPLY c3 b)
    SELECT
    YearTab.YearTest
    ,Easter.easterSunday
    FROM
    (
    SELECT TOP (100)
    YearTest = CAST( (ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ) -1) + 1950 AS SMALLINT)
    FROM c4
    ) YearTab
    CROSS APPLY [dbo].[ufn_inltab_getGregorianEasterSunday] ( YearTest ) Easter 

GO

    -- ================================== find day test ============================ --
    -- N-table on the fly:
    WITH c1(N) AS ( SELECT N FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS N(N) )
    ,c2(N) AS (SELECT a.N FROM c1 a CROSS APPLY c1 b)
    ,c3(N) AS (SELECT a.N FROM c2 a CROSS APPLY c2 b)
    ,c4(N) AS (SELECT a.N FROM c3 a CROSS APPLY c3 b)
    SELECT
    DayTab.CalenderDate
    ,Easter.isEasterSunday
    FROM
    (
    SELECT TOP (10000)
    CalenderDate = DATEADD(D, ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ), '2010-01-01' )
    FROM c4
    ) DayTab
    CROSS APPLY [dbo].[ufn_inltab_findGregorianEasterSunday] ( CalenderDate ) Easter
    WHERE MONTH(DayTab.CalenderDate) IN (3,4)

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)