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

Answer

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))