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:
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
;
(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'
;
