All quarters, Month year for pas 10 years in sql

Question

I want to retrieve all the quarters in the past 10 years using the query -

SELECT TO_CHAR(sysdate, 'yyyy" Q "')||level AS QuarterDate 
   FROM dual
CONNECT BY level <= 4 

Using above query I will get quarter for current year. I want for past 10 years.

Similary, is there a way to get all months in past 10 years in the format-

SELECT DISTINCT to_char(sysdate, 'yyyy / mm') from dual

Similarly, is there a way to get past all 10 years.

Answer 1

you can generate years, months, quarters, days, etc using the same connect by trick and just joining them together:

select
    to_char(y.n) || '/' || to_char(m.n)
from
    (select 1900+level as n from dual connect by level <= 500) y,
    (select level as n from dual connect by level <= 12) m
where
    y.n between 2012 and 2022

You can also just generate a large set of dates and filter/format the ones you want using the extensive format codes:

select
    to_date('1900-01-01', 'YYYY-MM-DD') + level as d
from
    dual
connect by
    level < 100000