SQL query for Month and Year

Question

Looking for an Oracle SQL query to show Month and Year starting from the current year- 1y and current year+1y. Eg: December 2019, January 2020, February 2020,......December 2021

Answer 1

There are multiple methods for doing this. I think simple examples like this are a good opportunity to learn about recursive CTEs:

with dates(yyyymm, n) as (
      select trunc(sysdate, 'Mon') as yyyymm, 1 as n
      from dual
      union all
      select add_months(yyyymm, -1), n + 1
      from dates
      where n <= 12
     )
select yyyymm
from dates;

Answer 2

You can use the hierarchy query as follows:

SQL> SELECT trunc(ADD_MONTHS(ADD_MONTHS(sysdate,-12), LEVEL-1), 'Mon') as  month_year
  2    FROM DUAL CONNECT BY LEVEL <= 24 + 1;

MONTH_YEAR
--------------
December  2019
January   2020
February  2020
March     2020
April     2020
May       2020
June      2020
July      2020
August    2020
September 2020
October   2020
November  2020
December  2020
January   2021
February  2021
March     2021
April     2021
May       2021
June      2021
July      2021
August    2021
September 2021
October   2021
November  2021
December  2021

25 rows selected.

SQL>

Answer 3

WITH d AS (
    SELECT
        'JAN' m,
        2021 y
    FROM
        dual
), d1 AS (
    SELECT
        to_date(m || y, 'MONYYYY') first_day,
        last_day(to_date(m || y, 'MONYYYY')) last_day1,
        last_day(to_date(m || y, 'MONYYYY')) - to_date(m || y, 'MONYYYY') no_of_days
    FROM
        d
)
SELECT
    level - 1 + first_day dates
FROM
    d1
CONNECT BY
    level <= no_of_days + 1;