Snowflake Creating a List of Dates

Question

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

[Generating date ranges with ARRAY_GENERATE_RANGE](https://stackoverflow.com/a/76141254/5070879) — Lukasz Szozda, Apr 30 '23 at 12:28

Felipe Hoffa · Accepted Answer · 2021-03-03T00:40:51.560

13

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

edited Mar 03 '21 at 00:40

answered Mar 03 '21 at 00:32

Felipe Hoffa

54,922
16
151
325

I am new to Snowflake, where does "SELECT" rank on this list: https://docs.snowflake.com/en/sql-reference/constructs/qualify.html I am trying to digest your query. – Chicken Sandwich No Pickles Mar 03 '21 at 16:35
`SELECT` is a mix in this case. I introduced `qualify` because `generator(rowcount => 10000 )` will produce too many rows, so then I remove all the rows not within the date range. A subquery would have worked too instead of using `qualify`, but this is more compact. – Felipe Hoffa Mar 03 '21 at 18:30

Gordon Linoff · Answer 2 · 2021-03-03T01:30:39.957

3

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

edited Mar 03 '21 at 01:30

answered Mar 02 '21 at 23:34

Gordon Linoff

1,242,037
58
646
786

1

Careful here! `seq4()` could produce non-sequential numbers. "This function uses sequences to produce a unique set of increasing integers, but does not necessarily produce a gap-free sequence." https://docs.snowflake.com/en/sql-reference/functions/seq1.html – Felipe Hoffa Mar 03 '21 at 00:34
@FelipeHoffa . . . If I use `row_number()`, what is the appropriate `order by`? `order by seq4()`? `order by NULL`? – Gordon Linoff Mar 03 '21 at 01:25

Snowflake Creating a List of Dates

2 Answers2

Linked