Search code examples
sqlcalendartemp-tablessnowflake-cloud-data-platform

Snowflake SQL - Create Temporary Date Table for EoM dates

I'm only a self-taught data-querying guy and am wholly unfamiliar with creating tables and such. The database I'm working on does have a calendar table, but it's only a forward moving calendar moving three years out. I am needing to create a date table for end of month records between two dates, including before the system dates table begins.

How best can one create this in Snowflake SQL?

Thank you much

Solution

  • This will create N end of month records. You can change the start date and change N to be the delta between your dates.

    select 
   row_number() over (order by null)  id, 
   add_months('2020-01-01'::date, + id) - 1 
from table(generator(rowcount => 100))