How to create months range in Snowflake?

Question

I want to create a month's range from a month of start date till the current month. Below I am setting a variable for the start and end timestamps.

My query does not return the beginning month of the period which is 2017-09-01 00:00:00.000. How can I get it worked?

SET sbf_num_months_1 =  (SELECT datediff(month, DATE_TRUNC('month', '2017-09-01'::date), DATE_TRUNC('month', CURRENT_DATE)));

  SELECT
  dateadd(
   month,
   '-' || row_number() over (order by null),
   dateadd(month, 1, DATE_TRUNC('month', CURRENT_DATE))
   )::timestamp AS month
    FROM table (generator(rowcount => ($sbf_num_months_1)))

Answer 1

You need to add 1 to your rowcount. Think of this logically, if you wanted to a month range of this month and last month, if you subtract those 2 months, you'll get 1. However, you want to generate 2 records, so you need to add 1 to the rowcount.

Also, this might be an easier select:

  SELECT dateadd(month,-seq8(0), DATE_TRUNC('month', CURRENT_DATE))::timestamp AS month
  FROM table (generator(rowcount => ($sbf_num_months_1)+1))