SQL query to get the output in the format "YYYY/ MM" for first date of the year till now

Question

I want to create a query to get the below output

2022 / 01
2022 / 02
2022 / 03
2022 / 04
2022 / 05
2022 / 06
2022 / 07
2022 / 08

i.e. all months in above format till the current month

how to get tweak my query to get the above output

select  
    (To_char(sysdate, 'yyyy') || ' / ' || To_char(sysdate, 'mm'))
from dual

Answer 1

You could use a hierarchical query:

select to_char(add_months(trunc(sysdate, 'YYYY'), level - 1), 'YYYY/MM') as result
from dual
connect by level <= extract(month from sysdate)

extract(month from sysdate) gives you the current month number, which is 8, as the limit for the hierarchy. At each level the required number of months - level - 1 - is added to the first day of the year.

Or you could use recursive subquery factoring:

with rcte (month_start) as (
  select trunc(sysdate, 'YYYY')
  from dual
  union all
  select month_start + interval '1' month
  from rcte
  where month_start < trunc(sysdate, 'MM')
)
select to_char(month_start, 'YYYY/MM') as result
from rcte
order by month_start

The anchor member gets the first day of the year. The recursive member adds a month to the previous result, until the start of the current month has been generated.

Both give the same result:

RESULT
2022/01
2022/02
2022/03
2022/04
2022/05
2022/06
2022/07
2022/08

db<>fiddle