'Easter / Good Friday for Sowflake Date Dim

Further Evolved : The code below satisfies what was wanted in earlier comments [if you follow history] :), my new question right below is stumping me.

Question: I want to have 'Week_Of_Year' Adjust every Sunday, the code below does it every Monday.. I have tried 1 and 0 but via alter session per Snowflake but no luck! Any idea how to have a new week triggered by Sunday not Monday (as that is what it is currently)?

ie the result I want is for Date:1/2/2005 to reflect Week_Of_Year as 2 , not 1. alter session set week_of_year_policy = 1; vs alter session set week_of_year_policy = 0;
WORKING CODE

create or replace temporary table test_temptable 
  ( 
   DATE_ID                 SMALLINT               NOT NULL
  ,FULL_DATE               DATE                   NOT NULL
  ,DATE                    Varchar(10)            NOT NULL
  ,YEAR                    SMALLINT               NOT NULL
  ,WEEK_OF_YEAR            SMALLINT               NOT NULL
  ,DAY_OF_YEAR             SMALLINT               NOT NULL
  ,QTR_NUMBER              SMALLINT               NOT NULL
  ,DAY_OF_QUARTER          SMALLINT               NOT NULL 
  ,MONTH_OF_YEAR           SMALLINT               NOT NULL
  ,MONTH_NAME              CHAR(3)                NOT NULL --need to have full month name, if it comes to it maybe do if logic
  ,DAY_OF_MONTH            SMALLINT               NOT NULL
  ,DAY_OF_WEEK             VARCHAR(9)             NOT NULL
  ,DAY_NAME                VARCHAR(12)            NOT NULL
  ,DAY_IS_WEEKDAY          boolean                NOT Null 
  ,DAY_IS_LAST_OF_MONTH    boolean                NOT Null
  ,DAY_OF_WEEK_IN_MONTH    SMALLINT               NOT NULL
  ,HOLIDAYUSA              VARCHAR(80)                       ----left out NOT NULL on Purpose
 --- ,DAY_IS_HOLIDAY          boolean                NOT NULL 

)
AS
    WITH MY_DATES AS (
    SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE 
    ,(seq8()+ 1) AS date_id
    ,DATE_TRUNC('QUARTER',Full_DATE) as Q
    ,DATEDIFF('day',Q, Full_DATE) as Day_of_Quarter
     /*logic to support Easter Day calculation */
    ,Full_DATE as SinCurDay
    ,MONTH(Full_Date) as inCurMonth
    ,YEAR(Full_Date) as inCurYear
    ,FLOOR(inCurYear/100) as inCurCent
    ,inCurYear%19 as inYear
    ,FLOOR((inCurCent-17)/25) as inYearTmp
    ,(inCurCent-FLOOR(inCurCent/4)-FLOOR((inCurCent-inYearTmp)/3)+(19*inYear)+15)%30 as inTemp2a
    ,inTemp2a-FLOOR(inTemp2a/28)*(1 - FLOOR(inTemp2a/28)*FLOOR(29/(inTemp2a+1))*FLOOR((21-inYear)/11)) as inTemp2b
    ,(inCurYear+FLOOR(inCurYear/4)+inTemp2b+2-inCurCent+FLOOR(inCurCent/4))%7 as inTemp3
    ,inTemp2b-inTemp3 as inTemp4
    ,3+FLOOR((inTemp4+40)/44) as inEastMontha
    ,inTemp4+28-31*FLOOR(inEastMontha/4) as inEastDay
    ,inEastMontha /*- 1*/ as inEastMonthb 
    ,Date_from_parts(inCurYear,inEastMonthb, inEastDay) as EasterDay
    /*End Easter Day Logic */

     /*Day of Week in Month*/
     /* CASE
            WHEN Day(Full_Date) < 8 THEN 1
            WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
            WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
            WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
            ELSE 5 End as Day_In_Month
      */

      FROM TABLE(GENERATOR(ROWCOUNT=>365))  
  )
  SELECT date_id
        ,Full_Date
        ,to_varchar(Full_Date, 'mm/dd/yyyy')
        ,YEAR(Full_Date)
        ,WEEKOFYEAR(Full_Date) 
        ,DAYOFYEAR(Full_Date)
        ,QUARTER(Full_Date)
        ,Day_Of_Quarter + 1
        ,MONTH(Full_Date)
        ,MONTHNAME(Full_Date)
        ,DAY(Full_Date)
        ,DAYOFWEEK(Full_Date) + 1
        ,DAYNAME(Full_Date)
        /*Weekend boolean */
        ,CASE 
            WHEN DAYOFWEEK(Full_date) + 1 = 7 THEN FALSE
            WHEN DAYOFWEEK(Full_date) + 1 = 1 THEN FALSE
         ELSE TRUE END
        /*Last Day Of Month Boolean*/
        ,CASE 
            WHEN Full_Date = last_day(Full_Date) THEN True 
         ELSE FALSE END
       /*Week in Month*/
       ---,CAST(Round((day(Full_Date) +6)/7,0) as VARCHAR)
       ,CASE
            WHEN Day(Full_Date) < 8 THEN 1
            WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
            WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
            WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
            ELSE 5 End

       /*HolidayUSA Logic */
       ,CASE 
            WHEN MONTH(Full_Date) = 10 AND DAY(Full_Date) = 31 THEN 'Halloween'
            /*ThanksGiving*/
            WHEN MONTH(Full_Date) = 11 AND DAYOFWEEK(Full_Date) + 1 = 5 AND 
                CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 End = 4 
            THEN 'Thanksgiving Day' -- should I add ()

            WHEN MONTH(Full_Date) = 11 AND DAYOFWEEK(Full_Date) + 1 = 6 AND 
                CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 End = 4 
            THEN 'Black Friday' -- should I add ()



            WHEN MONTH(Full_Date) = 12 AND DAY(Full_Date) = 25 THEN 'Christmas Day'
            WHEN MONTH(Full_Date) = 7 AND DAY(Full_Date) = 4 THEN 'Independence Day'
            WHEN MONTH(Full_Date) = 12 AND DAY(Full_Date) = 31 THEN 'New Years Eve'
            WHEN MONTH(Full_Date) = 1 AND DAY(Full_Date) = 1 THEN 'New Years Day'
            WHEN MONTH(Full_Date) = 5 AND DAYOFWEEK(Full_Date)+ 1 = 2 AND Day(Full_Date) > '24' then 'Memorial Day'
            WHEN MONTH(Full_Date) = 9 AND DAYOFWEEK(Full_Date) + 1 = 2 AND Day(Full_Date) < '8'THEN 'Labor Day'

            /*Martin Luther King Jr Day */
            WHEN MONTH(Full_Date) = 1 AND DAYOFWEEK(Full_Date) + 1 = 2 AND 
            CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 End 
             = 3 THEN 'Martin Luther King Jr Day'


            /*Presidents Day*/
            WHEN MONTH(Full_Date) = 2 AND DAYOFWEEK(Full_Date) + 1 = 2 AND
            CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 END
            = 3  THEN 'Presidents Day'






            WHEN MONTH(Full_Date) = 11 AND DAY(Full_Date) = 11 THEN 'Veterans Day' 


            /*Mothers Day */

            WHEN MONTH(Full_Date) = 5 AND DAYOFWEEK(Full_Date) + 1 = 1 AND
            CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 END
            =  2 THEN 'Mothers Day'

            /*Fathers Day */
            WHEN MONTH(Full_Date) = 6 AND DAYOFWEEK(Full_Date) + 1 = 1 AND
            CASE 
                    WHEN Day(Full_Date) < 8 THEN 1
                    WHEN Day(Full_Date) BETWEEN 7 AND 14 then 2
                    WHEN Day(Full_Date) BETWEEN 14 AND 21 then 3
                    WHEN Day(Full_Date) BETWEEN 21 AND 28 then 4 
                ELSE 5 END
            = 3  THEN 'Fathers Day'




            WHEN MONTH(Full_Date) = 2 AND DAY(Full_Date) = 14 THEN 'Valentines Day'
            WHEN Full_Date = EasterDay THEN 'Easter Day'
            WHEN Full_Date = EasterDay - 2 THEN 'Good Friday'
        ELSE NULL END
      --- ,CASE
      ---      WHEN HOLIDAYUSA is not NULL THEN TRUE Else False
     ---   END

    FROM MY_DATES
    Order By Full_Date;

Evolved Question: Please view updated code, where I translated the T-SQL logic to Calculate Easter into something Snowflake could understand. The only issue is that for Year 2005 I am off by one day ( the code below returns March 28, 2005 for Easter but it is March 27, 2008).

Question: Can someone help me understand why the below line is so close but wrong, I have tried so many variations of rtrim, RIGHT, and other manipulations to the values that feed the date thinking it had to do with rounding down, but all making the return easter dates further off. Below is my closest for a 20 year span , all within about 1-5 days of actual.

Line that is causing error:date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6

Easter Logic

(24 + 19 * (YR % 19)) % 30 AS EpactCalc, 
        EpactCalc - (EpactCalc / 28) AS PaschalDaysCalc, 
        PaschalDaysCalc - ((YR + YR / 4 + PaschalDaysCalc - 13) % 7) AS NumOfDaysToSunday, 
        3 + (NumOfDaysToSunday + 40) / 44 AS EasterMonth, 
        NumOfDaysToSunday + 28 - (31 * (EasterMonth / 4)) AS EasterDay, ---EasterMonth + RTRIM(YR) as test6
        ---to_date_from_parts(YR,(("0" + EasterMonth).substr(-2)), (("0" + EasterDay).substr(-2)) as test6
        date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6

Full Script:

CREATE OR REPLACE
TEMPORARY TABLE .test_temptable (Date_Id SMALLINT NOT NULL ,Full_Date DATE NOT NULL ,Date Varchar(10) NOT NULL ,YEAR SMALLINT NOT NULL ,WEEK_OF_YEAR SMALLINT NOT NULL ,DAY_OF_YEAR SMALLINT NOT NULL ,QTR_Number SMALLINT NOT NULL ,Day_Of_Quarter SMALLINT NOT NULL,MONTH_OF_YEAR SMALLINT NOT NULL ,MONTH_NAME CHAR(3) NOT NULL --need to have full month name, if it comes to it maybe do if logic
 ,DAY_OF_MONTH SMALLINT NOT NULL ,DAY_OF_WEEK VARCHAR(9) NOT NULL ,DAY_NAME VARCHAR(12) NOT NULL ,DAY_IS_WEEKDAY boolean NOT NULL,DAY_IS_LAST_OF_MONTH boolean NOT NULL ,DAY_OF_WEEK_IN_MONTH SMALLINT NOT NULL ,HOLIDAYUSA VARCHAR(80) ----left out NOT NULL on Purpose
 ,test1 smallint NOT NULL,test2 smallint NOT NULL ,test3 smallint NOT NULL ,test4 smallint NOT NULL,test5 smallint NOT NULL ,test6 DATE NOT NULL) AS WITH CTE_MY_DATE AS
  (---Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1, 2, 4, or 8 byte)..??I'd like to understand this a tad bit better.Is SEQ4 for float?
 SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE, 
        YEAR(Full_Date) AS YR, 
        (seq8()+ 1) AS date_id, 
        DATE_TRUNC('QUARTER',Full_DATE) AS q, 
        DATEDIFF('day',q, Full_DATE) AS Day_of_Quarter, 
        (24 + 19 * (YR % 19)) % 30 AS EpactCalc, 
        EpactCalc - (EpactCalc / 28) AS PaschalDaysCalc, 
        PaschalDaysCalc - ((YR + YR / 4 + PaschalDaysCalc - 13) % 7) AS NumOfDaysToSunday, 
        3 + (NumOfDaysToSunday + 40) / 44 AS EasterMonth, 
        NumOfDaysToSunday + 28 - (31 * (EasterMonth / 4)) AS EasterDay, ---EasterMonth + RTRIM(YR) as test6
        ---to_date_from_parts(YR,(("0" + EasterMonth).substr(-2)), (("0" + EasterDay).substr(-2)) as test6
        date_from_parts(YR, '0' + rtrim(EasterMonth),'0' + rtrim(EasterDay)) AS test6 


   FROM TABLE(GENERATOR(ROWCOUNT=>9125)) 
 ) 
SELECT date_id ,
       Full_Date ,
       to_varchar(Full_Date, 'mm/dd/yyyy') ,
       YEAR(Full_Date) ,
       WEEKOFYEAR(Full_Date) ,
       DAYOFYEAR(Full_Date) ,
       QUARTER(Full_Date) ,
       Day_Of_Quarter + 1 ,
       MONTH(Full_Date) ,
       MONTHNAME(Full_Date) ,
       DAY(Full_Date) ,
       DAYOFWEEK(Full_Date) + 1 ,
       DAYNAME(Full_Date) ---calculates if it is on weekend or not
 ,
       CASE 
           WHEN DAYOFWEEK(Full_date) = 7 THEN FALSE 
           WHEN DAYOFWEEK(Full_date) = 1 THEN FALSE 
           ELSE TRUE 
       END ----calculates if last day of month 
 ,
       CASE 
           WHEN Full_Date = last_day(Full_Date) THEN TRUE 
           ELSE FALSE 
       END,
       CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) -- this is what week you are in in the month, double check that what it ought to be 
 --- calculates holidays, is Thxgiving always in the fifth week?, 
 ,
       CASE 
           WHEN MONTH(Full_Date) = 10
                AND DAY(Full_Date) = 31 THEN 'Halloween' 
           WHEN MONTH(Full_Date) = 11
                AND DAYOFWEEK(Full_Date) + 1 = 4
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 5 THEN 'Thanksgiving Day' 
           WHEN MONTH(Full_Date) = 12
                AND DAY(Full_Date) = 25 THEN 'Christmas Day' 
           WHEN MONTH(Full_Date) = 7
                AND DAY(Full_Date) = 4 THEN 'Independence Day' --adding

           WHEN MONTH(Full_Date) = 12
                AND DAY(Full_Date) = 31 THEN 'New Years Eve' 
           WHEN MONTH(Full_Date) = 1
                AND DAY(Full_Date) = 1 THEN 'New Years Day' ---memorial day attempt

           WHEN MONTH(Full_Date) = 5
                AND DAYOFWEEK(Full_Date)+ 1 = 2
                AND Day(Full_Date) > '24' THEN 'Memorial Day' ---labor day

           WHEN MONTH(Full_Date) = 9
                AND DAYOFWEEK(Full_Date) + 1 = 2
                AND Day(Full_Date) < '8'THEN 'Labor Day' 
           WHEN MONTH(Full_Date) = 1
                AND DAYOFWEEK(Full_Date) = 2
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Martin Luther King Jr Day' 
           WHEN MONTH(Full_Date) = 2
                AND DAYOFWEEK(Full_Date) = 2
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Presidents Day' 
           WHEN MONTH(Full_Date) = 11
                AND DAY(Full_Date) = 11 THEN 'Veterans Day' ---added Mother's Day

           WHEN MONTH(Full_Date) = 5
                AND DAYOFWEEK(Full_Date) + 1 = 1
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 2 THEN 'Mothers Day'
           WHEN MONTH(Full_Date) = 6
                AND DAYOFWEEK(Full_Date) + 1 = 1
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Fathers Day'
           WHEN MONTH(Full_Date) = 2
                AND DAY(Full_Date) = 14 THEN 'Valentines Day' ---easter
 ---good friday

           ELSE NULL
       END ,
       EpactCalc ,
       PaschalDaysCalc ,
       NumOfDaysToSunday ,
       EasterMonth ,
       EasterDay ,
       test6
FROM CTE_MY_DATE;

Old Question: I have this nice piece of code for snowflake users that I need a little help finishing. I specifically want to use the second chunk of code that was written for SqlServer to be used in Snowflakes env, and integrated into my script below (first chunk of code).

Specifically: a)"How do you integrate a function into a query like this" as in Advice where to put the code inside my script above because I am having trouble understanding how to integrate a function within a select statement b)"Is there anything glaring about this query that would make running it in Snowflake uniquely difficult" I attempted to run the SQLServer "easter date" code alone inside snowflake, and changed the variables to match snowflakes requirements (ie take out @) and then I got an error unexpected 'BEGIN'.

    CREATE OR REPLACE
TEMPORARY TABLE test_temptable (Date_Id SMALLINT NOT NULL ,Full_Date DATE NOT NULL ,Date Varchar(10) NOT NULL ,YEAR SMALLINT NOT NULL ,WEEK_OF_YEAR SMALLINT NOT NULL ,DAY_OF_YEAR SMALLINT NOT NULL ,QTR_Number SMALLINT NOT NULL ,Day_Of_Quarter SMALLINT NOT NULL,MONTH_OF_YEAR SMALLINT NOT NULL ,MONTH_NAME CHAR(3) NOT NULL 
 ,DAY_OF_MONTH SMALLINT NOT NULL ,DAY_OF_WEEK VARCHAR(9) NOT NULL ,DAY_NAME VARCHAR(12) NOT NULL ,DAY_IS_WEEKDAY boolean NOT NULL,DAY_IS_LAST_OF_MONTH boolean NOT NULL ,DAY_OF_WEEK_IN_MONTH SMALLINT NOT NULL ,HOLIDAYUSA VARCHAR(80) 
  (
 SELECT DATEADD(DAY, SEQ4(), '2005-01-01') AS Full_DATE, 
        (seq8()+ 1) AS date_id, 
        DATE_TRUNC('QUARTER',Full_DATE) AS q, 
        DATEDIFF('day',q, Full_DATE) AS Day_of_Quarter

   FROM TABLE(GENERATOR(ROWCOUNT=>366)) 
 ) 
SELECT date_id ,
       Full_Date ,
       to_varchar(Full_Date, 'mm/dd/yyyy') ,
       YEAR(Full_Date) ,
       WEEKOFYEAR(Full_Date) ,
       DAYOFYEAR(Full_Date) ,
       QUARTER(Full_Date) ,
       Day_Of_Quarter + 1 ,
       MONTH(Full_Date) ,
       MONTHNAME(Full_Date) ,
       DAY(Full_Date) ,
       DAYOFWEEK(Full_Date) + 1 ,
       DAYNAME(Full_Date)
 ,
       CASE 
           WHEN DAYOFWEEK(Full_date) = 7 THEN FALSE 
           WHEN DAYOFWEEK(Full_date) = 1 THEN FALSE 
           ELSE TRUE 
       END 
 ,
       CASE 
           WHEN Full_Date = last_day(Full_Date) THEN TRUE 
           ELSE FALSE 
       END,
       CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) 
 ,
       CASE 
           WHEN MONTH(Full_Date) = 10
                AND DAY(Full_Date) = 31 THEN 'Halloween' 
           WHEN MONTH(Full_Date) = 11
                AND DAYOFWEEK(Full_Date) + 1 = 4
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 5 THEN 'Thanksgiving Day' 
           WHEN MONTH(Full_Date) = 12
                AND DAY(Full_Date) = 25 THEN 'Christmas Day' 
           WHEN MONTH(Full_Date) = 7
                AND DAY(Full_Date) = 4 THEN 'Independence Day' --adding

           WHEN MONTH(Full_Date) = 12
                AND DAY(Full_Date) = 31 THEN 'New Years Eve' 
           WHEN MONTH(Full_Date) = 1
                AND DAY(Full_Date) = 1 THEN 'New Years Day' ---memorial day attempt

           WHEN MONTH(Full_Date) = 5
                AND DAYOFWEEK(Full_Date)+ 1 = 2
                AND Day(Full_Date) > '24' THEN 'Memorial Day' ---labor day

           WHEN MONTH(Full_Date) = 9
                AND DAYOFWEEK(Full_Date) + 1 = 2
                AND Day(Full_Date) < '8'THEN 'Labor Day' 
           WHEN MONTH(Full_Date) = 1
                AND DAYOFWEEK(Full_Date) = 2
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Martin Luther King Jr Day' 
           WHEN MONTH(Full_Date) = 2
                AND DAYOFWEEK(Full_Date) = 2
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Presidents Day' 
           WHEN MONTH(Full_Date) = 11
                AND DAY(Full_Date) = 11 THEN 'Veterans Day' ---added Mother's Day

           WHEN MONTH(Full_Date) = 5
                AND DAYOFWEEK(Full_Date) + 1 = 1
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 2 THEN 'Mothers Day'
           WHEN MONTH(Full_Date) = 6
                AND DAYOFWEEK(Full_Date) + 1 = 1
                AND CAST(Round((day(Full_Date) +6)/7,0) AS VARCHAR) = 3 THEN 'Fathers Day'
           WHEN MONTH(Full_Date) = 2
                AND DAY(Full_Date) = 14 THEN 'Valentines Day' ---easter
 ---good friday

           ELSE NULL
       END
FROM CTE_MY_DATE;

Below is the SQLServer code I need help to input the above!! (thanks Function to return date of Easter for the given year)

    CREATE FUNCTION dbo.GetEasterSunday 
( @Y INT ) 
RETURNS SMALLDATETIME 
AS 
BEGIN 
    DECLARE     @EpactCalc INT,  
        @PaschalDaysCalc INT, 
        @NumOfDaysToSunday INT, 
        @EasterMonth INT, 
        @EasterDay INT 

    SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30 
    SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 
    SET @NumOfDaysToSunday = @PaschalDaysCalc - ( 
        (@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7 
    ) 

    SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 

    SET @EasterDay = @NumOfDaysToSunday + 28 - ( 
        31 * (@EasterMonth / 4) 
    ) 

    RETURN 
    ( 
        SELECT CONVERT 
        (  SMALLDATETIME, 
                 RTRIM(@Y)  
            + RIGHT('0'+RTRIM(@EasterMonth), 2)  
            + RIGHT('0'+RTRIM(@EasterDay), 2)  
        ) 
    ) 

END 
GO


Solution 1:[1]

New answer: You must use month and day numerical values directly, not reformat as TEXT:

DATE_FROM_PARTS(Year, EasterMonth, EasterDay)

Old answer:
It should be fairly simple to convert your T-SQL function to a Snowflake JavaScript function.
Maybe you have to learn JavaScript on the way, though.

The skeleton of such a function can be:

CREATE OR REPLACE FUNCTION GetEasterSunday(Y FLOAT) RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
  var EpactCalc = (24 + 19 * (Y % 19)) % 30;
  // more stuff here
  var EasterMonth = 4, EasterDay = 21;
  return Y + "-" + ("0" + EasterMonth).substr(-2) + "-" + ("0" + EasterDay).substr(-2);
$$;
SELECT GetEasterSunday(2019)::DATE; 

Solution 2:[2]

It seems that T-SQL truncates values when you use integers. It does not take into count the fractional part at all.

In T-SQL it goes like this:

select -2 + 28 - (31 * (3 / 4)) as [EasterDay]

Returns: 26

In snowflake

select -2 + 28 - (31 * (3 / 4)) as [EasterDay]

Returns: 2.75

But if you truncate the value 3 / 4 = 0.75 , it gives same as T-SQL (because if you omit the fractional part it is trunc(0.75)=0:

select -2 + 28 - (31 * trunc(3 / 4)) as [EasterDay]

Returns: 26

So in T-SQL like this:

WITH years as(
    SELECT   2009 AS [Year]
    UNION ALL
    SELECT   yl.[Year] + 1 AS [Year]
    FROM     years yl
    WHERE    yl.[Year] + 1 <= YEAR(dateadd(year,5,GETDATE()))
), 
e AS 
(
 SELECT d.[Year], [EasterDate] = CONVERT(DATE, RTRIM([Year]) + '0' + RTRIM([Month]) 
        + RIGHT('0' + RTRIM([Day]),2))
      FROM (SELECT [Year],[Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
      FROM (SELECT [Year],[Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
      FROM (SELECT [Year],DaysToSunday = paschal - (([Year] + [Year] / 4 + paschal - 13) % 7 )
      FROM (SELECT [Year],paschal = epact - (epact / 28)
      FROM (SELECT years.[Year], epact = (24 + 19 * (years.[Year] % 19)) % 30 from years)  AS epact) AS paschal) AS dts) AS m) AS d
  )
  select *
  from(
  SELECT [Year],[EasterDate], HolidayName = 'Easter Sunday' FROM e
    UNION ALL SELECT [Year],DATEADD(DAY,-2,[EasterDate]), 'Good Friday'   FROM e
    UNION ALL SELECT [Year],DATEADD(DAY, 1,[EasterDate]), 'Easter Monday' FROM e
    UNION ALL SELECT [Year],DATEADD(DAY, 39,[EasterDate]), 'Ascension Day' FROM e --NOTE! 1973–1991 this was moved to always to previous saturday!
    UNION ALL SELECT [Year],DATEADD(DAY, 49,[EasterDate]), 'Whit Sunday' FROM e --NOTE! 7th sunday from Easter Sunday
  ) easter
  order by easter.[Year],[EasterDate]

In Snowflake like this

WITH years as(
    --select distinct "YearNumber" as "Year" FROM dm.D_DAY
    select year(dateadd(year, seq4(), dateadd(year,-13,current_date()))) as "Year"
    from
    table(generator(rowcount => 19))
), 
e AS 
(
 SELECT "EasterYear","EasterMonth", "EasterDay","DaysToSunday","epact","paschal",
      DATE_FROM_PARTS("EasterYear", "EasterMonth", "EasterDay") as "EasterDate"
      FROM (SELECT "EasterYear","epact","paschal","EasterMonth" AS "EasterMonth","DaysToSunday", "DaysToSunday" + 28 - (31 * trunc("EasterMonth" / 4)) as "EasterDay"
      FROM (SELECT "EasterYear","epact","paschal",trunc(3 + ("DaysToSunday" + 40) / 44,0) as "EasterMonth", "DaysToSunday" 
      FROM (SELECT "EasterYear","epact","paschal","paschal" - trunc(mod(("EasterYear" + "EasterYear" / 4 + "paschal" - 13),7 )) as "DaysToSunday" 
      FROM (SELECT "EasterYear","epact","epact" - trunc("epact" / 28) as "paschal"
      FROM (SELECT years."Year" as "EasterYear", mod((24 + (19 * mod(years."Year",19))),30) as "epact" from years)  AS "epact") AS "paschal") AS "dts") AS "m") AS d
 )
  select *
  from(       SELECT "EasterYear","EasterDate",'Easter Sunday' as "HolidayName" FROM e
    UNION ALL SELECT "EasterYear",DATEADD(DAY,-2,"EasterDate"), 'Good Friday'       FROM e
    UNION ALL SELECT "EasterYear",DATEADD(DAY, 1,"EasterDate"), 'Easter Monday'     FROM e
    UNION ALL SELECT "EasterYear",DATEADD(DAY, 39,"EasterDate"), 'Ascension Day'    FROM e --NOTE! 1973–1991 this was moved to always to previous saturday!
    UNION ALL SELECT "EasterYear",DATEADD(DAY, 49,"EasterDate"), 'Whit Sunday'      FROM e --NOTE! 7th sunday from Easter Sunday
  ) easter
  order by easter."EasterYear","EasterDate"
  

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2