Stored Procedure with Date parameter failed with error: “Unsupported type for binding argument 2”

It is common that we will need to perform Date transformations inside Stored Procedures in SQL. Recently I have encountered an error that Snowflake does not like the format that is generated by Javascript inside Stored Procedure code, and resulted an error. See below example:

CREATE OR REPLACE PROCEDURE test_sp (MYDATE DATE)
RETURNS VARCHAR(255)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var result = snowflake.execute(
    { 
        sqlText: 'SELECT COALESCE(:1, NULL)', 
        binds: [MYDATE]
    }
);

result.next();

return result.getColumnValue(1)
$$;

To execute this Stored Procedure, simply run below query:

CALL test_sp(CURRENT_DATE());

And we can see it will fail with below error:

Execution error in store procedure TEST_SP: Invalid binds argumentMon Dec 07 2020 00:00:00 GMT+0000 (UTC). Error: Unsupported type for binding argument 2undefined At Snowflake.execute, line 2 position 23

We can see that Snowflake tried to pass value “Mon Dec 07 2020 00:00:00 GMT+0000 (UTC)” to the bind variable, and resulted in error.

After researching, I found out that Javascript’s Date.toString() function returns non-standard date string format, like the one we see above “Mon Dec 07 2020 00:00:00 GMT+0000 (UTC)“. It was mentioned in the StackOverflow post here: Convert date into sfdate for snowflake. And it also provided a solution, which is to convert the format to the one that Snowflake recognizes. See below the correct version of code:

CREATE OR REPLACE PROCEDURE test_sp (MYDATE DATE)
RETURNS VARCHAR(255)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var result = snowflake.execute(
    { 
        sqlText: 'SELECT COALESCE(:1, NULL)', 
        binds: [MYDATE.toISOString()]
    }
);

result.next();

return result.getColumnValue(1)
$$;

The change is basically update “binds: [MYDATE]” to “binds: [MYDATE.toISOString()]“. Then the Stored Procedure can execute correctly:

CALL test_sp(CURRENT_DATE());

+--------------------------+
| TEST_SP                  |
|--------------------------|
| 2020-12-07T00:00:00.000Z |
+--------------------------+

Hope this information can be useful.