Oracle - get previous, current and next year from query-CodePudding

Is below example good solution for get previous, current and next year?

with cte as(
    Select extract(year from sysdate - 365) as year from dual
    union all
    Select extract(year from sysdate) as year from dual
    union all
    Select extract(year from sysdate   365) as year from dual
)
Select * from cte


YEAR
-----
2020
2021
2022

Or any better solution?

CodePudding user response：

Well, yours isn't correct for e.g. leap years on the last day of that year. Look at 2020:

SQL> select
  2    extract(year from to_date('31.12.2020', 'dd.mm.yyyy') - 365) result
  3  from dual;

    RESULT
----------
      2020

SQL>

See? Turns out that "previous" year for date 31.12.2020 is still 2020.

ADD_MONTHS is safer, I guess:

SQL> select
  2    extract (year from add_months(to_date('31.12.2020', 'dd.mm.yyyy'), -12)) result
  3  from dual;

    RESULT
----------
      2019

SQL>

SQL> select
  2    extract (year from add_months(trunc(sysdate), -12)) previous,
  3    extract (year from add_months(trunc(sysdate),   0)) this,
  4    extract (year from add_months(trunc(sysdate),  12)) next
  5  from dual;

  PREVIOUS       THIS       NEXT
---------- ---------- ----------
      2020       2021       2022

SQL>

(this, of course, doesn't require add_months, but I kept it to make query look prettier).

Or, why not simply

SQL> select this - 1 as previous,
  2         this,
  3         this   1 as next
  4  from (select extract(year from sysdate) as this from dual);

  PREVIOUS       THIS       NEXT
---------- ---------- ----------
      2020       2021       2022

SQL>

For 3 rows, use a CTE:

SQL> with temp (this) as
  2    (select extract(year from sysdate) from dual)
  3  select this - 1 as year from temp union all
  4  select this             from temp union all
  5  select this   1         from temp;

      YEAR
----------
      2020
      2021
      2022

SQL>

CodePudding user response：

I might overlook something but why not just this?

with cte as(
    Select extract(year from sysdate) - 1 as year from dual
    union all
    Select extract(year from sysdate)     as year from dual
    union all
    Select extract(year from sysdate)   1 as year from dual
)
Select * from cte