Reputation: 688
I am setting session variable
SET MY_LOAD_DATE = '1999-01-01T10:10:10';
SELECT TO_DATE('1999-01-01T10:10:10.0') as d; // this command will return proper date output
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);
Stored Procedure returns following
Fri Jan 01 1999 00:00:00 GMT-0800 (Pacific Standard Time)
What is happening inside stored procedure, why I am getting different output for same SELECT TO_DATE statement.
................ Answer ...............
I have converted string date to Date and then used toLocaleDateString method to get only date from datetime.
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();
$$;
This will return 1/1/1999
Upvotes: 1
Views: 772
Reputation: 176324
"What is happening inside stored procedure, why I am getting different output for same SELECT TO_DATE statement."
SQL and JavaScript UDFs provide similar, but different, data types, based on their native data type support. Objects within Snowflake and JavaScript are transferred using the following mappings.
...
All timestamp and date types are converted into JavaScript Date() objects. The JavaScript date type is equivalent to TIMESTAMP_LTZ(3) in Snowflake SQL.
...
SQL DATE is converted to JavaScript Date representing midnight of the current day in the local time zone.
The conversions are as follow:
CREATE OR REPLACE PROCEDURE MY_ID_UPDATE(ARG STRING)
as stringTO_DATE(...)
as dateresult.getColumnValue(1);
Date() objectRETURNS STRING
Date() object converted back to stringSidenote: Concatenating string to execute could lead to SQL Injection
var result = snowflake.execute({sqlText: `SELECT TO_DATE('`+ ARG +`') as d`});
Better way is to provide parametrized query:
var result = snowflake.execute({sqlText: `SELECT TO_DATE(?) as d`
,binds: [ARG]});
Upvotes: 1