Snowflake Javascript UDF

Question

what's wrong with the below snowflake udf?

create or replace function compare_date(input_date date)
returns varchar
language javascript
as
$$
    var sql_command = "select to_date('09/30/2011', 'MM/DD/YYYY')";
    var result;
    var stmt = snowflake.createStatement( {sqlText: sql_command} );
    var resultSet = stmt.execute();
    result=resultSet.next();

    if (input_date > result.getColumnValue(1)) {
        return 'Y'
    } else {
       return 'N'
    }
$$;

select compare_date('2023-02-21');

I get the below error when I call the udf. Do you have any suggestions on what I'm missing?

100132 (P0000): JavaScript execution error: Uncaught ReferenceError: snowflake is not defined in COMPARE_DATE at 'var stmt = snowflake.createStatement( {sqlText: sql_command} );' position 11

Answer 1

If you want to encapsulate this in a UDF, you can use a SQL UDF and something like the IFF function to do the date comparison and return of Y or N:

create or replace function compare_date(input_date date)
returns varchar
language sql
as
$$
   iff(input_date > to_date('09/30/2011', 'MM/DD/YYYY'), 'Y', 'N')
$$;

select compare_date('2023-02-21'); -- Y
select compare_date('2009-07-04'); -- N