I need to create a column months between two days

EX :

CodePudding user response：

That's some kind of a row generator. As you tagged the question with Oracle tag, one option is

SQL> with test (start_date, end_date) as
  2    (select date '2022-01-01', date '2022-12-30' from dual)
  3  select add_months(start_date, level - 1) as month
  4  from test
  5  connect by level <= trunc(months_between(end_date, start_date))   1;

MONTH
----------
2022.01.01
2022.02.01
2022.03.01
2022.04.01
2022.05.01
2022.06.01
2022.07.01
2022.08.01
2022.09.01
2022.10.01
2022.11.01
2022.12.01

12 rows selected.

SQL>

CodePudding user response：

Snowflake SQL

with t0(start_date, end_date) as (
  select * from values
  ('2022-01-01'::date, '2022-12-30'::date)
),
t1 as (
  select row_number() over(order by 1) as i
  from table(generator(rowcount => 12))
)
select t0.start_date, t0.end_date, add_months(t0.start_date, t1.i - 1) as "MONTH"
from t0 cross join t1
where "MONTH" <= t0.end_date;