Snowflake datetime conversion

Question

I have a stored procedure that has a parameter called rundate. If I use this parameter in my stored procedure I get date like Tue May 03 2022 08:37:42 GMT-0500 (Central Daylight Time) which is not allowing for me to perform any manipulation. How to convert this data (with Javascript stored procedure) to valid TZ datetime like 2022-05-03 15:07:21.000 +0000?

Please advise

var strsql = `
      SELECT
                 WHEN j_date >RUN_DATE THEN RUN_DATE
          ELSE j_date
        
      FROM table
     where col1 = 'ABC'
      ;
  `;
  // Run the statement.
  var sql = snowflake.createStatement({sqlText: strsql });
  var result = sql.execute();
  result.next();
Copy

Answer 1

1. You should be able to convert to a timestamp with your specific mask format. This site has a reference of all the formats you can specify: https://docs.snowflake.com/en/user-guide/date-time-input-output.html#timestamp-formats. I am presuming your date column is currently a string
2. the SQL statement you have above needed a CASE and END to properly execute.

Please see demo table below

   create or replace table table_a (j_date varchar, run_date varchar, col1  varchar);
      INSERT INTO table_a  (SELECT 'Tue May 03 2022 08:37:42 GMT-0500', 'Tue May 03 2022 08:37:42 GMT-0500' ,'ABC') ;
Copy

With this, you can then wrap a TO_TIMESTAMP around your date column and see both results side by side

   SELECT CASE
             WHEN J_DATE > RUN_DATE THEN RUN_DATE
             ELSE j_date
          END new_col,
          to_timestamp(new_col,'DY MON DD YYYY HH24:MI:SS GMT-TZHTZM') new_col_formatted
      FROM table_a
     where col1 = 'ABC';
Copy

NEW_COL	NEW_COL_FORMATTED
Tue May 03 2022 08:37:42 GMT-0500	2022-05-03 08:37:42.000

Answer 2

The problem is that inside Javascript the default representation of a date is not compatible with the SQL side of the game. Made worse is the fact that the Javascript runtime cares about the timezone of the server, which for some horrific reason is not UTC, so you will get different values to region.

If you need to pass DATE types into the SP/Function pass it as string from the SQL side, so the date will already be formatted as you want it, and just string compose that into your SQL:

You can even see this in the stock Javascript SP example: the date is passed as a string..

I was working up an example to show this:

create database test;
use schema test.public;

create table test.public.table1(id number, date date);

INSERT INTO test.public.table1 
    SELECT * FROM VALUES 
        (1,   '2022-01-01'),
        (2  , '2022-02-01'),
        (3  , '2022-02-05');

    
    
create or replace procedure date_handler(rundate DATE) 
    returns string
    language javascript
as
$$
    let strsql = `select sum(id) from test.public.table1 where date >= '`+ RUNDATE +`'`;
    return strsql;
$$;

call date_handler('2020-01-01'::date);
Copy

DATE_HANDLER
select sum(id) from test.public.table1 where date >= 'Wed Jan 01 2020 00:00:00 GMT-0800 (Pacific Standard Time)'

create or replace procedure string_handler(rundate text) 
    returns string
    language javascript
as
$$
    let strsql = `select sum(id) as a from test.public.table1 where date >= '`+ RUNDATE +`'`;
    return strsql;
    
$$;

call string_handler('2020-01-01'::date); 
Copy

STRING_HANDLER
select sum(id) as a from test.public.table1 where date >= '2020-01-01'

select sum(id) as a from test.public.table1 where date >= '2020-01-01';
Copy

A
6

But was shocked the doc's show this, but don't appear to mention it directly..

Answer 3

The simplest thing to use is the .toISOString() method for JavaScript dates.

create or replace procedure JAVASCRIPT_DATE("inputDate" date)
returns string
language javascript 
as
$$
    return inputDate;
$$;

call JAVASCRIPT_DATE('2022-05-04'::date);
-- Returns Wed May 04 2022 00:00:00 GMT+0000 (Coordinated Universal Time)

create or replace procedure ISO_DATE("inputDate" date)
returns string
language javascript 
as
$$
    return inputDate.toISOString();
$$;

call ISO_DATE('2022-05-04'::date);
-- Returns 2022-05-04T00:00:00.000Z
Copy

Although the .toISOString() method adds a T between the date and time, Snowflake will parse it just fine. The time will be represented in UTC this way. If that's not what you want, be sure to adjust for time zone.