This code works perfectly in SSMS, but in Snowflake, not so much. Any suggestions on how I can fix it?
set (start_date) = ('2017-07-01');
set (end_date) = ('2022-06-30');
with get_all_dates as (
select
$start_date as DateValue
, 1 as level
union all
select
DATEADD(DAY,1,DateValue)
, level + 1
from
get_all_dates
where
Datevalue < $end_date
)
select * from get_all_dates;
This produces the following error message:
SQL compilation error: Type mismatch between anchor and recursive term of column 'DATEVALUE' in Recursive CTE 'GET_ALL_DATES'
Expected output:
2017-07-01
2017-07-02
...
2022-06-29
2022-06-30
Gordon's answer is useful, but beware -- seq4() is not guaranteed to produce sequential numbers. That means you could get a series of disparate dates instead of the desired result.
You can do this instead:
select '2020-01-01'::date+x
from (
select row_number() over(order by 0) x
from table(generator(rowcount => 1824))
)
Shorter version:
select '2020-01-01'::date + row_number() over(order by 0) x
from table(generator(rowcount => 1824))
With an arbitrary start_date and end_date:
select -1 + row_number() over(order by 0) i, start_date + i generated_date
from (select '2020-01-01'::date start_date, '2020-01-15'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date