Oracle sql – get months id between two dates

I have date range eg. ‘2021-01-05’ and ‘2021-02-10’. Two months January and February.

Need resaults:

Months
------
1
2

Answer

You want to iterate through the months. This is done with a recursive query in SQL:

with months (month_start_date) as
(
  select trunc(:start_date, 'month') from mytable
  union all
  select month_start_date + interval '1' month
  from months
  where month_start_date < trunc(:end_date, 'month')
)
select
  extract(year from month_start_date) as year,
  extract(month from month_start_date) as month
from months
order by month_start_date;