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)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating