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