CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowsql
Sains
Sains

Reputation: 477

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

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'
]

Upvotes: 8

Views: 4210

Answers (4)

Adrian White
Adrian White

Reputation: 1804

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 
      )

enter image description here

Upvotes: 0

Rich Murnane
Rich Murnane

Reputation: 2920

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"]

Upvotes: 4

FedSic
FedSic

Reputation: 143

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
)
$$;

Upvotes: 6

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2850

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.

Upvotes: 7

Related Questions