Snowflake使用datetime变量查询时出现<redacted>错误求助

查询Snowflake表的特定日期范围时，硬编码日期能正常执行，但使用datetime变量时返回<redacted>错误，无额外提示。尝试过Date、DateTime、DateTimeLTZ、Varchar等多种数据类型，均无法解决。Snowflake报错信息不够直观，官方文档未提供有效指引。

复现代码如下：

/*
-- Set up necessary tables

CREATE OR REPLACE TABLE CHARGE_F (charge_item_id int, updt_dt_tm timestampltz);

INSERT INTO charge_f values (1, '2022-09-01');
INSERT INTO charge_f values(2, '2022-09-05');
INSERT INTO charge_f values(3, '2022-09-10');

SELECT * FROM charge_f 

CREATE OR REPLACE TABLE TMP_CHARGEITEMID_LIST (Idx integer IDENTITY(1, 1), charge_item_id integer) ;

*/

EXECUTE IMMEDIATE 
$$
DECLARE
    start_dt_tm datetime ;
    end_dt_tm datetime  ;
BEGIN 
    start_dt_tm := '2022-09-01' ;
    end_dt_tm := dateadd(week, 1, start_dt_tm) ;
    
    -- Works without a problem 
    INSERT INTO TMP_CHARGEITEMID_LIST (charge_item_id)
    SELECT charge_item_id FROM CHARGE_F c WHERE c.UPDT_DT_TM BETWEEN '2022-09-01' AND '2022-09-08'  ;
    
    /* -- Causes <redacted> error 
    INSERT INTO TMP_CHARGEITEMID_LIST (charge_item_id)
    SELECT charge_item_id FROM CHARGE_F c WHERE c.UPDT_DT_TM BETWEEN start_dt_tm And end_dt_tm  ;
    */
    
    RETURN start_dt_tm ;
END ;
$$

问题根源是变量类型与表字段类型不匹配：表中UPDT_DT_TM是TIMESTAMPLTZ（带时区的时间戳），而存储过程中声明的datetime类型实际对应Snowflake的TIMESTAMP_NTZ（无时区时间戳），两者隐式转换时触发错误。

解决方法有两种：

• 直接将变量类型改为TIMESTAMPLTZ，与表字段类型保持一致
• 在查询中显式将变量转换为TIMESTAMPLTZ类型

修正后的代码（方法一：匹配变量类型） #

EXECUTE IMMEDIATE 
$$
DECLARE
    start_dt_tm timestampltz ;
    end_dt_tm timestampltz  ;
BEGIN 
    start_dt_tm := '2022-09-01' ;
    end_dt_tm := dateadd(week, 1, start_dt_tm) ;
    
    -- 现在可以正常执行
    INSERT INTO TMP_CHARGEITEMID_LIST (charge_item_id)
    SELECT charge_item_id FROM CHARGE_F c WHERE c.UPDT_DT_TM BETWEEN start_dt_tm And end_dt_tm  ;
    
    RETURN start_dt_tm ;
END ;
$$

修正后的代码（方法二：显式转换） #

如果需要保留datetime变量类型，可在查询中转换：

EXECUTE IMMEDIATE 
$$
DECLARE
    start_dt_tm datetime ;
    end_dt_tm datetime  ;
BEGIN 
    start_dt_tm := '2022-09-01' ;
    end_dt_tm := dateadd(week, 1, start_dt_tm) ;
    
    -- 显式转换变量类型匹配表字段
    INSERT INTO TMP_CHARGEITEMID_LIST (charge_item_id)
    SELECT charge_item_id FROM CHARGE_F c 
    WHERE c.UPDT_DT_TM BETWEEN CAST(start_dt_tm AS TIMESTAMPLTZ) And CAST(end_dt_tm AS TIMESTAMPLTZ)  ;
    
    RETURN start_dt_tm ;
END ;
$$

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