CODEWITHSUNDEEP
javascriptsqlsnowflake-cloud-data-platformsnowflake-schema
Natasha
Natasha

Reputation: 195

Javascript function in snowflake to append tablename with current date

I have recently started to use snowflake and have been stuck at this issue:

I want to clone a table called AB_USER to AB_USER_(current_date). I have written following code to accomplish this: 

CREATE or replace PROCEDURE backup_proc()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  var tab_name = `AB_USER_BCK_2020_`+ current_date();
  stat = `create or replace table staging.` + tab_name + ` clone staging.AB_USER`;
  var rs = snowflake.execute( { sqlText: stat} );
  return 'Done.';
  $$;

The problem is I cannot find appropriate function to get current date. Snowflake provides a JS environment but I don't know which function to use to get current date.

I am very new to snowflake so any help in this will be much appreciated.

Thanks.

Upvotes: 0

Views: 877

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10059

CURRENT_DATE is an SQL command, so you need to call it as SQL statement with snowflake.execute.

As I see, you want to get values of month and day from current date, so you can use the following procedure:

CREATE or replace PROCEDURE backup_proc()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var curdate = snowflake.execute( { sqlText: "SELECT TO_CHAR(CURRENT_DATE,'MMDD') as curdate"}  );
curdate.next();
var tab_name = "AB_USER_BCK_2020_"+ curdate.getColumnValue('CURDATE');
var stat = "create or replace table staging." + tab_name + " clone staging.AB_USER";
var rs = snowflake.execute( { sqlText: stat} );
return "Done.";
$$
;

Upvotes: 1

Related Questions