Snowflake error when using datetime variable in a query

I am trying to query a database table for a specific date range. When the dates are hardcoded, the query works without any problem, however, when I try to use the variables, Snowflake is returning an error message “”, there is no additional information.

I have tried using Date, DateTime, DateTimeLTZ, Varchar datatypes but so far nothing has worked. Snowflake error messages are not as friendly as SQL Server or Oracle, documentation doesn’t offer much help either.

Here is a sample code to replicate the issue, any help or guidance is highly appreciated

/*
-- 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 ;
$$

Answer

To use variable in this context, it should be prefixed with : and it is documented at Using a Variable in a SQL Statement (Binding):

You can use a variable in a SQL statement. (This is sometimes referred to as binding a variable.) Prefix the variable name with a colon. For example:

INSERT INTO my_table (x) VALUES (:my_variable)

Note that if you are using a variable in an expression or with a Snowflake Scripting language element (e.g. RETURN), you do not need to prefix the variable with a colon.

The code:

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;

Output – Snowsight (please note a different color of variables):

---