Snowflake Creating a List of Dates

user_5560898 0
Viewed 2193

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
snowflake-cloud-data-platform sql
2 Answers

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

enter image description here

user_1324380

You can generate a bunch of numbers and generate the dates based on that:

select dateadd(day, row_number() over (order by null), '2017-07-01')
from table(generator(rowcount => 1824)) g
user_11440350
Related