sqlgenerate-seriessnowflake-cloud-data-platform

generate_series() equivalent in snowflake

I'm trying to find the snowflake equivalent of generate_series() (the PostgreSQL syntax).

SELECT generate_series(timestamp '2017-11-01', CURRENT_DATE, '1 day')
Solution

  • This is how I was able to generate a series of dates in Snowflake. I set row count to 1095 to get 3 years worth of dates, you can of course change that to whatever suits your use case

    select 
    dateadd(day, '-' || seq4(), current_date()) as dte 
from 
    table 
       (generator(rowcount => 1095))

    Originally found here

    EDIT: This solution is not correct. seq4 does not guarantee a sequence without gaps. Please follow other answers, not this one. Thanks @Marcin Zukowski for pointing that out.