Partition by date in Snowflake procedure removing leading zeros from date

Question

I am trying to partition the data from a Snowflake table and storing it as a parquet file in a S3 bucket. This partition allows me to store the data organised by date (Bucket:///). I am performing this, using the below Snowflake procedure:

However, this creates the folders structure in the following way:

And I wanted it to be: year=2022/month=05/day=01/

To accomplish this I changed month and day variables to:

However, it produced the same result that I was having before the change. I even tried to assign some values to both 'day' and 'month' vars like: var day = '01' var month = '05' But it is still removing the leading zeros from the month and day.

Do you know how can I solve this?

Thanks for your help.

Answer 1

You just need to add some additional lines of code to prefix the values with zero when required e.g. (pseudo-code)

day = if day < 10 then "0"||day else day;
month = if month < 10 then "0"||month else month;

Answer 2

You can use a Snowflake SQL to get the correct date format in MM and DD. Here is the SQL

SELECT TO_CHAR(CURRENT_DATE(), 'MM') MON, TO_CHAR(CURRENT_DATE(), 'DD');

and the stored proc to get the values

create or replace procedure date_test()
  returns string
  language javascript
  as
  $$
  var day;
  var mon;
  // Dynamically compose the SQL statement to execute.
  var sql_command = "SELECT TO_CHAR(CURRENT_DATE(), 'MM') MON, TO_CHAR(CURRENT_DATE(), 'DD');"
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
    res.next();
  mon = res.getColumnValue(1); 
  day = res.getColumnValue(2);
  return "day:" + day + " month:" + mon;
  $$
  ;

call date_test();

output:

DATE_TEST
day:13 month:05