CODEWITHSUNDEEP
sqldatabasesnowflake-cloud-data-platformsnowflake-schema
Fisseha Berhane
Fisseha Berhane

Reputation: 2653

Create date array from start date and end date columns in Snowflake

I want to create an array of dates from two columns in my table in Snowflake. In the example below, how can I add a third columns that contains an array of all dates between start_date and end_date? It can work using a UDF but I do not have permission to do that.

 select current_date as start_date, dateadd(day, 10, current_date) as end_date

This is the result I want: enter image description here

Upvotes: 3

Views: 698

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

Getting to this query wasn't straightforward, but it works:

select init_date, end_date
  , array_agg(init_date+x-1) within group (order by x asc)
from date_ranges
join (select x from (select row_number() over(order by 0) x from table(generator(rowcount => 1000))))
where x<=end_date-init_date+1
group by init_date, end_date
;

enter image description here

(I had to join with a series of consecutive numbers to get the desired results)

You can test with this setup:

create or replace temp table date_ranges as
select '2019-12-29'::date init_date, '2020-01-03'::date end_date
union all select '2021-02-26', '2021-03-02'
;

Upvotes: 3

Related Questions