Convert date into sfdate for snowflake

Question

I'm having problems working with dates in Snowflake using Snowflake's Javascript.

One select queries and gets a date. It's declared in the source table as a date. I want to be able to add 1 day to this date, then insert that into a DATE field in another table. However, I'm getting a bind error on this. With help from another stackoverflow, I got the returned type and found it to be a string, and not an sfDate.

How can I convert the returned string from a select into a date that I can work with? A bonus question is how can I add one day to this value?

Also tried to convert it to date with try_to_date as below:

                    var convertedRunningDate = Date("Sun Jul 29 2018 00:00:00 GMT-0700 (PDT)");
                    statement = snowflake.createStatement(
                        {
                            sqlText: " insert into mytable(date_stamp)  values(try_to_date(?)) "
                            ,binds: convertedRunningDate 
                        }
                    );

and without it:

                    var convertedRunningDate = Date("Sun Jul 29 2018 00:00:00 GMT-0700 (PDT)");
                    statement = snowflake.createStatement(
                        {
                            sqlText: " insert into mytable(date_stamp)  values(?) "
                            ,binds: convertedRunningDate 
                        }
                    );

and I get

Invalid binds argumentSun Jul 29 2018 00:00:00 GMT-0700 (PDT). Error: Unsupported type for binding argument 2undefined

in both cases

Unknown Unknown · Accepted Answer · 2020-06-29T08:42:46

The default JavaScript Date.toString() representation converts the date to a string in a format that is non-standard. Also, to use a Date constructor ensure you pass the new keyword as otherwise Date acts as a function that rejects invalid inputs and silently returns the current date.

> console.log(Date("Sun Jul 29 2018 00:00:00 GMT-0700 (PDT)"))
"Mon Jun 29 2020 01:16:31 GMT-0700 (PDT)"

> console.log(new Date("Sun Jul 29 2018 00:00:00 GMT-0700 (PDT)"))
"Sun Jul 29 2018 00:00:00 GMT-0700 (PDT)"

When using the default Date string formatting from JavaScript in SQL to be cast as date or timestamp types, Snowflake only recognises standard formatted strings, and will reject JS date strings of the form Sun Jul 29 2018 00:00:00 GMT-0700 (PDT). Use Date.toISOString() instead to produce a usable representation that will work with Snowflake SQL types.

Furthermore, the binds argument in snowflake.createStatement(…) must always be an array of elements, even when you are planning to pass only a single element to it. That is, supply binds: [convertedRunningDate] instead of binds: convertedRunningDate.

Combining the two above points, here's an example that shows how to manipulate the date within JavaScript from one table to another.

create or replace table source_table(datecol date) as select current_date;

create or replace table dest_table (datecol date);

CREATE OR REPLACE PROCEDURE insert_date_plus_one() 
RETURNS boolean 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Grab a datecol from a select query
    var source_date_stmt = snowflake.createStatement(
      {
        sqlText: "select datecol from source_table"
      }
    );
    var source_resultset = source_date_stmt.execute();
    source_resultset.next();
    
    // This is of type SfDate because it came from a query ResultSet,
    // so we can apply standard JS Date functions over it
    var source_date = source_resultset.getColumnValue(1);
    
    // Function to increment a Date object by one standard day
    // Sourced from https://stackguides.com/questions/563406/add-days-to-javascript-date
    function addDaysInJs(date, days) {
      var result = new Date(date);
      result.setDate(result.getDate() + days);
      return result;
    }
    
    var dest_date = addDaysInJs(source_date, 1);
    
    // Insert the incremented date using its ISO representation string
    // which will allow Snowflake to grok it properly
    var dest_date_stmt = snowflake.createStatement(
      {
        sqlText: "insert into dest_table values (?)"
        ,binds: [dest_date.toISOString()]
      }
    );
    var dest_resultset = dest_date_stmt.execute();
    var did_insert_run = dest_resultset.next();
    
    return did_insert_run;
$$ ;

call insert_date_plus_one();

This yields the result:

> select * from source_table;
+------------+
| DATECOL    |
|------------|
| 2020-06-29 |
+------------+
> select * from dest_table;
+------------+
| DATECOL    |
|------------|
+------------+

> call insert_date_plus_one();
+----------------------+
| INSERT_DATE_PLUS_ONE |
|----------------------|
| TRUE                 |
+----------------------+

> select * from dest_table;
+------------+
| DATECOL    |
|------------|
| 2020-06-30 |
+------------+

If you need to arbitrarily use Date objects instead of sourcing it from a table row, just ensure you are constructing them properly (with new), and when binding use its Date.toISOString() method instead of passing it as-is (default toString() produces an incompatible format).

Note: Where possible, try to manipulate it over SQL and just extract results so you do not have to work with two different date/time type systems.

Further reading:

Convert date into sfdate for snowflake

1 Answers