CODEWITHSUNDEEP
snowflake-cloud-data-platform
Sarde
Sarde

Reputation: 688

Snowflake TO_DATE or DATE not returning expected output from Stored Procedure

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

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

"What is happening inside stored procedure, why I am getting different output for same SELECT TO_DATE statement."

JavaScript Data Types:

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:

  1. ARG: CREATE OR REPLACE PROCEDURE MY_ID_UPDATE(ARG STRING) as string
  2. SQL: TO_DATE(...) as date
  3. JavaScript: result.getColumnValue(1); Date() object
  4. JavaScript stored procedure; RETURNS STRING Date() object converted back to string

Sidenote: 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

Related Questions