Snowflake存储过程中TO_DATE/DATE返回结果不符合预期问题

问题现象 #

首先设置会话变量：

SET MY_LOAD_DATE = '1999-01-01T10:10:10';

SELECT TO_DATE('1999-01-01T10:10:10.0') as d; -- 该语句可返回正确的日期输出

直接执行上述语句可正常返回日期结果 1999-01-01。

随后创建如下存储过程：

CREATE OR REPLACE PROCEDURE MY_ID_UPDATE(ARG STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
    $$
        var result = snowflake.execute({sqlText: `SELECT TO_DATE('`+ ARG +`') as d`});
        result.next();
        return result.getColumnValue(1);
        return my_date;
    $$;
    
CALL MY_ID_UPDATE($MY_LOAD_DATE);

调用该存储过程后，返回结果为 Fri Jan 01 1999 00:00:00 GMT-0800 (Pacific Standard Time)。

不一致原因 #

相同的TO_DATE查询返回结果不同，是因为两类执行场景的返回值类型和序列化规则不同：

• 直接在SQL会话执行时，TO_DATE返回Snowflake原生的DATE类型，会按照会话默认的日期格式输出仅日期内容
• 在JavaScript存储过程中执行时，SQL返回的DATE类型会被自动隐式转换为JavaScript原生的Date对象，直接返回该对象时会默认调用Date的toString()方法，输出带时区、星期信息的完整时间字符串

解决方案 #

将查询得到的日期值转换为JavaScript Date对象后，调用toLocaleDateString方法即可提取仅日期部分，优化后的存储过程代码如下：

CREATE OR REPLACE PROCEDURE MY_ID_UPDATE(ARG STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
    $$
        var result = snowflake.execute({sqlText: `SELECT TO_DATE(?) as d`, binds: [ARG]});
        result.next();
        var my_date = result.getColumnValue(1);
        var d = new Date(my_date);
        return d.toLocaleDateString();
    $$;

调用优化后的存储过程可返回结果 1/1/1999。

内容的提问来源于stack exchange，提问作者Sarde