sqlsnowflake-cloud-data-platform

Snowflake Creating a List of Dates


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

Solution

  • 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