Snowflake Tasks causing error in timezone in queries

1
votes

I am running a simple insert query inside a stored procedure with to_timeatamp_ntz("column value") along with other columns. This works fine when I am running it with the snowflake UI and logged in with my account. This works fine when I am calling it using python scripts from my visual studio instance. The same stored procedure fails when it is being called by a scheduled task. I am thinking if it has something to do with the user's timezone of 'System' vs my time zone.

Execution error in store procedure LOAD_Data(): Failed to cast variant value "2019-11-27T13:42:03.221Z" to TIMESTAMP_NTZ At Statement.execute, line 24 position 57

I tried to provide timezone as session parameters in task and in the stored proc but does not seem to be addressing the issue. Any ideas?

2
stored-procedurestasksnowflake-cloud-data-platform
I did a task with "INSERT INTO TEST_TIMESTAMP(T1) SELECT TO_TIMESTAMP_NTZ('2019-11-27T13:42:03.221Z');" where the column was NTZ_TIMESTAMP(9) and it ran successfully. Am I missing something to repro? Let me know if you can setup a similar very simple task and see if it works. I wonder if it's specific to parameters or data. - Suzy Lockwood
Since you get an error message from the Statement.execute() call it would have been useful to include the statement that generates the error. - Hans Henrik Eriksen
Can you provide the code for the stored proc? - Simon D

2 Answers

1
votes

I'm guessing (since you didn't include the SQL statement that causes the error) that you are trying to bind a Date object when creating a Statement object. That won't work.

The only parameters you can bind are numbers, strings, null, and the special SfDate object that you can only get from a result set (to my knowledge). Most other parameters must be converted to string using mydate.toJSON(), JSON.stringify(myobj), etc., before binding, eg:

var stmt = snowflake.createStatement(
   { sqlText: `SELECT :1::TIMESTAMP_LTZ NOW`, binds: [(new Date).toJSON()] }
);

Date object errors can be misleading, because Date objects causing an error can be converted and displayed as strings in the error message.

0
votes

I found the issue: my Task was using a copy paste effect similar to this:

CREATE TASK TASK_LOAD_an_sp
  WAREHOUSE = COMPUTE_WH
  TIMEZONE = 'US/Eastern'  
SCHEDULE = 'USING CRON  0/30 * * * * America/New_York'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
    Call LOAD_an_sp();

The Timestamp input format was causing this.