Find Good Friday via Oracle stored procedure function

user_4334781 0
Viewed 100

I have this requirement to determine dates and one of them I need to filter out Good Friday (only) with a parameter of given year

v_temp_year     varchar2(4) :=  to_char(sysdate, 'YYYY');
//the function would be something below that returns date 
getGoodFridayDate(v_temp_year)

Now I need to formulate a function to be called in each stored procedure and I feel like a total noob about it. Any help would be greatly appreciated. Please don't tag as duplicate I cannot find solutions here with same requirement .

oracle sql
3 Answers
create or replace
function good_friday(p_year number) return date is
    l_day   pls_integer := 0;
    l_month pls_integer := 3;

    l_lunar pls_integer := mod(p_year,19);
    l_cent  pls_integer := p_year / 100;
    l_equi  pls_integer := mod((l_cent - floor(l_cent / 4) - floor((8 * l_cent + 13) / 25) + 19 * l_lunar + 15),30);
    l_full  pls_integer := l_equi - floor(l_equi / 28) * (1 - floor(l_equi / 28) * floor(29 / (l_equi + 1)) * floor((21 - l_lunar) / 11));

begin

    l_day   := l_full - mod((p_year + floor(p_year / 4) + l_full + 2 - l_cent + floor(l_cent / 4)),7) + 28;

    if l_day > 31 then
        l_month := 4;
        l_day   := l_day - 31;
    end if;
    return to_date(p_year||'-'||l_month||'-'||l_day,'YYYY-MM-DD')-2;
end;
/

SQL> select good_friday(2022) from dual;

GOOD_FRID
---------
15-APR-22

SQL> select good_friday(2011) from dual;

GOOD_FRID
---------
22-APR-11
user_73672930

My function looks slightly different:

CREATE OR REPLACE FUNCTION Easter(yyyy IN INTEGER DEFAULT TO_CHAR(SYSDATE, 'YYYY')) RETURN DATE IS

    a INTEGER;
    b INTEGER;
    c INTEGER;
    d INTEGER;
    e INTEGER;
    f INTEGER;
    g INTEGER;
    h INTEGER;
    i INTEGER;
    k INTEGER;
    l INTEGER;
    m INTEGER;
    n INTEGER;
    p INTEGER;
    EM INTEGER;
    ED INTEGER;

BEGIN

    a := yyyy MOD 19;
    b := TRUNC(yyyy / 100);
    c := yyyy MOD 100;
    d := TRUNC(b / 4);
    e := b MOD 4;
    f := TRUNC((b + 8) / 25);
    g := TRUNC((b - f + 1) / 3);
    h := (19*a + b - d - g + 15) MOD 30;
    i := TRUNC(c / 4);
    k := c MOD 4;
    l := (32 + 2*e + 2*i - h - k) MOD 7;
    m := TRUNC((a + 11*h + 22*l) / 451);
    n := TRUNC((h + l - 7*m + 114) / 31);
    p := (h + l - 7*m + 114) MOD 31;
    EM := n;
    ed := p + 1;
    
    RETURN TO_DATE(yyyy||'-'||EM||'-'||ed, 'YYYY-MM-DD');
END Easter;

Note, it returns Easter Sunday, so for Good Friday you need to subtract 2 days from result.

You may pick your favorite Algorithm and modify the function accordingly.

Comparing my algorithm with algorithm used by @Connor McDonald gives different result for years earlier than 1899 and later than 2049:

WITH t AS
    (SELECT 1850+LEVEL AS YEAR, good_friday(1850+LEVEL) AS good_friday_1, easter(1850+LEVEL)-2 good_friday_2
    FROM dual
    CONNECT BY LEVEL < 300)
SELECT *
FROM t
WHERE good_friday_1 <> good_friday_2;


+--------------------------------+
|YEAR|GOOD_FRIDAY_1|GOOD_FRIDAY_2|
+--------------------------------+
...
|1894|24.03.1894   |23.03.1894   |
|1895|13.04.1895   |12.04.1895   |
|1896|04.04.1896   |03.04.1896   |
|1897|17.04.1897   |16.04.1897   |
|1898|09.04.1898   |08.04.1898   |
|1899|01.04.1899   |31.03.1899   |
|2050|09.04.2050   |08.04.2050   |
|2051|01.04.2051   |31.03.2051   |
|2052|13.04.2052   |19.04.2052   |
|2053|05.04.2053   |04.04.2053   |
|2054|28.03.2054   |27.03.2054   |
...
+--------------------------------+
user_30272660

Just to verify this for my own curiosity I created this using the modified formula from New Scientist from here:

function good_friday(p_year integer) return date is
    a constant pls_integer := mod(p_year, 19);
    b constant pls_integer := floor(p_year / 100);
    c constant pls_integer := mod(p_year, 100);
    h constant pls_integer := mod(19*a + b - floor(b / 4) - floor((8*b + 13) / 25) + 15, 30);
    l constant pls_integer := mod(32 + 2*mod(b, 4) + 2*floor(c / 4) - h - mod(c, 4), 7);
    m constant pls_integer := floor((a + 11*h + 19*l) / 433);
    n constant pls_integer := floor((h + l - 7*m + 90) / 25);
    p constant pls_integer := mod(h + l - 7*m + 33*n + 19, 32);
begin
    if p_year is null then
        return null;
    elsif p_year < 1900 or p_year > 2099 then
        raise value_error;
    end if;
    return to_date(p_year||'-'||n||'-'||p,'YYYY-MM-DD')-2;
end good_friday;

Testing shows this agrees with Domscheit's answer, and always returns a Friday for the valid range of years.

user_1032950
Related