How do I construct an array of dates in a given date range using snowsql?

Question

Given two dates, I would like to create an array that holds all of the days between those dates and then filter it to contain only the days that fall at the end of a month.

For example, with start_date 2019-01-31 and end_date 2019-06-30, I would to construct the array

[
  '2019-01-31',
  '2019-02-28',
  '2019-03-31',
  '2019-04-30',
  '2019-05-31',
  '2019-06-30'
]

The dates in your example/array are strange, including non-existant 31-06-2019. I suppose you want consecutive dates, but your example looks like "almost end-of-month". Please update your question with correct details. — Hans Henrik Eriksen, Nov 11 '19 at 08:03

Hans Henrik Eriksen · Answer 1 · 2019-11-12T06:57:24.430

7

It's a little hard to understand exactly what you want, but from the requested output it looks like the array contains monthly dates between two date limits. I tried to implement using JavaScript to avoid the dreaded "Unsupported subquery" error, but it's hard to calculate dates, format them and return from JavaScript to SQL.
So I ended up with an SQL UDF:

CREATE OR REPLACE FUNCTION ARRAY_MONTHS_BETWEEN("FROM" DATE, "TO" DATE)
RETURNS ARRAY AS 
'
  SELECT ARRAY_AGG(DATEADD(MONTH, "MONTH" , "FROM")) A
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY NULL) - 1 "MONTH"
        FROM TABLE(GENERATOR(ROWCOUNT => 1000)))
  WHERE "MONTH" <= CEIL(DATEDIFF(MONTHS, "FROM", "TO"))
';

It can be tricky to use this function except with date constants, because you risk opening Pandora's box of Snowflake correlated subqueries. But then again, maybe not.

edited Nov 12 '19 at 06:57

answered Nov 11 '19 at 07:23

Hans Henrik Eriksen

2,690
5
12

nod we have a two processes that do similar stuff, one creates a temp table in the session with the "years worth of days" using a create table with constants dates inserted (using a {{mustache}} syntax) and another date dimension table that is years ahead/behind.. and use them to suit needs while avoid huge table scans. – Simeon Pilgrim Nov 12 '19 at 00:29
1

Thanks for the help. I managed to achieve that by creating a calendar table in Snowflake – Sains Nov 12 '19 at 03:19
"Rainbow tables" for date calculations are great eg. in a data warehousing environment. Using semi-structured data, eg. arrays, may simplify storage at the (slight?) expense of efficiency and handling. – Hans Henrik Eriksen Nov 12 '19 at 07:11

score 6 · Answer 2 · answered Nov 16 '19 at 23:56

The Snowflake LAST_DAY function can be used to fetch the last day of every month that is between the two dates https://docs.snowflake.net/manuals/sql-reference/functions/last_day.html.

SELECT 
  ARRAY_AGG(LAST_DAY(MY_DATE))
FROM (
  SELECT
    LAST_DAY(DATEADD(MONTH, SEQ4(), '2019-01-31')) AS MY_DATE
  FROM TABLE(GENERATOR(ROWCOUNT=>20000))
  WHERE MY_DATE <= '2019-06-30'
);

The above query will also wrap the result in an array https://docs.snowflake.net/manuals/sql-reference/functions/array_agg.html

[
  "2019-01-31",
  "2019-02-28",
  "2019-03-31",
  "2019-04-30",
  "2019-05-31",
  "2019-06-30"
]

As already suggested, you can optionally use this sql to create a new user-defined function:

CREATE FUNCTION LAST_DATES_TO_ARRAY(FROM_D DATE, TO_D DATE)
RETURNS ARRAY 
AS 
$$
SELECT ARRAY_AGG(LAST_DAY(MY_DATE))
FROM (
  SELECT
    LAST_DAY(DATEADD(MONTH, SEQ4(), FROM_D)) AS MY_DATE
  FROM TABLE(GENERATOR(ROWCOUNT=>20000))
  WHERE MY_DATE <= TO_D
)
$$;

This is pretty great! Thank you for your help, if you are interested the Snowflake Community has Select Star program that adds points to your profile if you are a register Snowflake user, check out the page and sign up if you are interested: https://community.snowflake.com/s/selectstar — Rachel McGuigan, Nov 25 '19 at 19:07

score 4 · Answer 3 · answered Nov 18 '19 at 12:55

Building on what previous responders have provided, the following should work for you.

CREATE OR REPLACE FUNCTION LAST_DATES_TO_ARRAY(FROM_DT DATE, TO_DT DATE)
RETURNS ARRAY 
AS 
$$
SELECT ARRAY_AGG(DISTINCT LAST_DAY(MY_DATE))
FROM (
  SELECT
    LAST_DAY(DATEADD(MONTH, SEQ4(), FROM_DT)) AS MY_DATE
  FROM TABLE(GENERATOR(ROWCOUNT=>2000000))
  WHERE MY_DATE <= TO_DT
  ORDER BY 1
)
$$;

SELECT LAST_DATES_TO_ARRAY(DATEADD('YEARS', -1, CURRENT_DATE()), CURRENT_DATE()) AS my_array;

 MY_ARRAY
["2018-11-30",
 "2018-12-31",
 "2019-01-31",
 "2019-02-28",
 "2019-03-31",
 "2019-04-30",
 "2019-05-31",
 "2019-06-30",
 "2019-07-31",
 "2019-08-31",
 "2019-09-30",
 "2019-10-31"]

Adrian White · Answer 4 · 2021-07-09T06:01:05.323

An alternative sql only solution - start and end dates go into the current_date() spots.

It's super quick to generate all the month ends for 10000 years placing today in the middle (365|180 * 10000) then just predicate the answer with sed start and end dates prior to placing into an array. runs in 202ms

No functions, uses cache, less testing, etc etc.

select array_agg(distinct my_date) 
from ( 
      SELECT 
      last_day( DATEADD(DAY, -1*SEQ4(), CURRENT_DATE()+(180*10000)),month) AS MY_DATE 
      FROM TABLE(GENERATOR(ROWCOUNT => (365*10000) ))  
      where 
          my_date between 
          current_date() -39 and 
          current_date() +80 
      )

How do I construct an array of dates in a given date range using snowsql?

4 Answers4