Snowflake UDF with JavaScript returns incorrect dates

Question

I came across an issue where my User Defined Function(UDF) implemented in Snowflake with JavaScript language returns invalid date, it gains a day.

To demonstrate problem, here is a simple UDF which takes three parameters year, month, day and returns Date object.

CREATE OR REPLACE FUNCTION JDC(YEAR FLOAT,MONTH FLOAT,DAY FLOAT)
RETURNS DATE
LANGUAGE JAVASCRIPT
AS
$$
   // In Java Script months start with 0 so -1
   return new Date(YEAR,MONTH-1,DAY)
$$
;

Calling this procedure returns correct values for all years except few days between years 1942 to 1949,

select JDC(1942,02,10); returns 1942-02-11 which is incorrect
select JDC(1948,12,31); returns 1949-01-01 which is incorrect
select JDC(2020,02,10); returns 2020-02-10, correct value

If you notice 1, 2 it gains one day during javascript date conversion to snowflake. I confirmed JavaScript able to return back right values by changing the UDF return type to VARCHAR and returning the date object as string like return new Date(YEAR,MONTH-1,DAY).toString(), so something going on during conversion.

By default JavaScript uses UTC timezone and my current snowflake instance set to run in PST time zone, so thinking timezone side the conversion should go back to previous day but surprisingly it goes other way.

user_5070879 · Answer

As a workaround you could use native functionality:

DATE_FROM_PARTS
DATE_FROM_PARTS( , ,  )
Creates a date from individual numeric components that represent the year, month, and day of the month.

SELECT DATE_FROM_PARTS(1942,2,10)

user_132438 · Answer

To avoid weird timezone conversions between your Snowflake session and JavaScript, set your own timezone to GMT:

ALTER SESSION SET TIMEZONE = 'America/New_York';
select JDC(1942,02,10);
# `1942-02-11` wrong

ALTER SESSION SET TIMEZONE = 'GMT';
select JDC(1942,02,10);
# `1942-02-10`, as expected.

Javascript is weird with dates, times, and timezones - as you noted with months being 0 based while the rest of the date in the constructor is not. Another problem is that Javascript doesn't have a "date" only constructor - as Date() represents a datetime instead.

From the docs:

JavaScript Date objects are converted to the UDF’s result data type, adhering to the same conversion semantics as casts from TIMESTAMP_LTZ(3) to the return data type. https://docs.snowflake.com/en/sql-reference/udf-js.html#dates

Snowflake UDF with JavaScript returns incorrect dates

2 Answers