Snowflake Variable & Timestamp

Question

I've done a lot of searching, but been unable to find the answer to the problem I have below. Which I'm sure has a very simple answer.

I'm trying to create a static timestamp, which I'll be able to use through out my stored procedure.

So in the instance below, I'm trying to write the CURRENT_TIMESTAMP() to a variable, then calling it in an update statement.

The output I get in the table is "[object Object]", this is currently a varchar column. Ideally want it to input into a timestamp column, but was getting an error.

CREATE OR REPLACE PROCEDURE SP_TEST_RUN()

RETURNS VARCHAR

LANGUAGE JAVASCRIPT AS $$

var STORE_DATE1= snowflake.execute({sqlText: "select CURRENT_TIMESTAMP()"});

snowflake.execute({sqlText: "UPDATE MY_TABLE SET TOKEN_1 = '" + STORE_DATE1 +"';" });

$$;

Many thanks in advance!

Answer 1

The execute command returns a resultset from which you need to select the value you want. There are lots of examples of this in the documentation - look for the getColumnValue method

Answer 2

With NickW help, managed to figure it out!

As NickW rightly points out, need to use getColumnValue, this will then loop though the results, as there is only 1 entry. Only needs to occur once.

One thing to note, I had to add "toISOString()" to the variable, which converts the JAVA variable to a TIMESTAMP SQL can understand, otherwise JAVA will automatically convert the Timestamp to a string.

CREATE OR REPLACE PROCEDURE SP_TEST_RUN() RETURNS VARCHAR LANGUAGE JAVASCRIPT

AS $$

var STORE_DATE1= snowflake.execute({sqlText: "select CURRENT_TIMESTAMP()"});

while (STORE_DATE1.next())

{
var STORE_DATE2 = STORE_DATE1.getColumnValue(1);    
}

snowflake.execute({sqlText: "UPDATE MY_TABLE SET INSERT_DATE_TIME = '" + STORE_DATE2.toISOString() +"';" });

$$;