UDF In Snowflake SQL

Question

Not sure why doesn't this work:

CREATE OR REPLACE FUNCTION fnc_FiscalYear(AsOf DATETIME) RETURNS INT AS

$$ 
    DECLARE Answer INT; 
BEGIN 
    CASE 
    WHEN MONTH(AsOf) < 7 THEN 
        Answer := YEAR(AsOf)
    ELSE 
        Answer := YEAR(AsOf) + 1 
    END; 
    Return Answer; 
END; 
$$;

Not sure why this isn't compiling. I keep getting: syntax error line 2 at position 12 unexpected 'Answer'. (line 2)

I have years of experience in TSQL but snowflake gives me fits. Thanks in advance.

Answer 1

The example of a SQL UDF takes the form:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;

Thus your SQL following this form would be:

CREATE OR REPLACE FUNCTION fnc_FiscalYear(AsOf DATETIME) 
RETURNS INT 
AS
$$ 
    YEAR(AsOf) + iff(MONTH(AsOf) < 7, 0, 1) 
$$;

and that is happy,

select  fnc_FiscalYear('2024-9-25'::date);

if you really want CASE then:

CREATE OR REPLACE FUNCTION fnc_FiscalYear(AsOf DATETIME) 
RETURNS INT 
AS
$$ 
    YEAR(AsOf) + case when MONTH(AsOf) < 7 then 0 else 1 end 
$$;