Generate Series of Dates in Snowflake


1 comment

  • Avatar
    Kf0 Ox1 (Edited )

    While the approach described above got me started thinking about a solution, it has 2 issues. The first one is some odd behavior with Snowflake sequences as outlined in the comments for this SO answer The second one is hard coding the number of days as mentioned by Charlotte already. 

    Both can be solved by using dateadd with parameters in a slightly different way (I'll paste it here, in case SO reorganizes their DB later). Feel free to like the answer there, if it solved your problem.  

    -- set parameter to be used as generator "constant" including the start day
    set num_days =  (Select datediff(day, TO_DATE('2017-11-01','YYYY-MM-DD'), current_date()+1));
    -- use parameter now
        '-' || row_number() over (order by null),
        dateadd(day, '+1', current_date())
      ) as date
    from table (generator(rowcount => ($num_days)));
    -- clean up previously set parameter variable
    unset num_days;


    Comment actions Permalink

Please sign in to leave a comment.