Creating a list of dates within WITH clause

I want to to create a list of every first day of every month starting from 2021-01-01 for the next 24 months.

With normal SQL, it has worked with these following codes that I had found online:

with mnt as (
  select 1 as n, convert(date,'20210101',112) as d
  union all
  select n   1, dateadd(month,1,d)
  from mnt
  where n < 24
)
select d from mnt

(You can also see it in dbfiddle)

However, when I tried that same logic on BigQuery it did not work, as the error stated that: Table "mnt" must be qualified with a dataset (e.g. dataset.table)

with mnt as (
  select 1 as n, CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD') as d
  union all
  select n 1, date_add(CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD'), INTERVAL 1 MONTH)
  from mnt
  where n < 24
)

select d as salesmonth from mnt

See the code on BigQuery

I also tried looping on Big Query

declare x DATE DEFAULT "2021-07-01";

REPEAT 
  Set x = date_add(x, INTERVAL 1 MONTH);
  SELECT x;
  until x > CAST('2023-07-01' AS DATE FORMAT 'YYYY-MM-DD')
END REPEAT;

but the results do not come out in tabular form, nor I know how to put them within a WITH clause.

Does anyone know how to fix this and adapt the above SQL query in Big Query?

CodePudding user response：

Below query using WITH RECURSIVE will work in your case.

with recursive mnt as (
  select 1 as n, DATE '2021-01-01' as d
  union all
  select n   1, date_add(d, INTERVAL 1 MONTH)
  from mnt
  where n < 24
)
select d as salesmonth from mnt;

But in BigQuery GENERATE_DATE_ARRAY() is more handy than recursive approach.

SELECT * 
  FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2022-12-01', INTERVAL 1 MONTH)) salesmonth

CodePudding user response：

Consider also below option

select date_add('2021-01-01', interval i month)
from unnest(generate_array(0, 23)) i