JavaScript UDF fails with error: TIME inside VARIANT value – not supported

When running Javascript UDF with VARIANT data type passed into it as parameter, it fails with error:

TIME inside VARIANT value – not supported

This issue can be reproduced using below steps:

-- Have a JS UDF that accepts VARIANT data type
CREATE OR REPLACE FUNCTION UDF_VARIANT_TIMESTAMP_TEST(V VARIANT)
  RETURNS VARIANT
  LANGUAGE JAVASCRIPT
  AS
  $$
    RETURN V;
  $$;

-- A table with VARIANT column
CREATE OR REPLACE TABLE TEST (A VARIANT);

-- Insert TIME value into VARIANT column
INSERT INTO TEST SELECT TO_VARIANT(OBJECT_CONSTRUCT('TIME', '00:00:00'::TIME));

-- Pass the VARIANT to the JS function will fail with error
SELECT UDF_VARIANT_TIMESTAMP_TEST(A) FROM TEST;

And the last SELECT statement will fail with above mentioned error.

This is because Javascript does not support the TIME data type, as it only has DATE data type, so Snowflake is NOT able to perform the conversion. To avoid unexpected errors, the type checking is enforced when VARIANT data type is being converted to Javascript’s Object.

The solution is to NOT use TIME data type in the VARIANT, use STRING instead. So below will work:

CREATE OR REPLACE TABLE TEST (A VARIANT);

INSERT INTO TEST SELECT TO_VARIANT(OBJECT_CONSTRUCT('TIME', '00:00:00'));

SELECT UDF_VARIANT_TIMESTAMP_TEST(A) FROM TEST;

Hope above can help!