First day of the next year with rolling year in Oracle

Question

I always need the 1st of the next year for a query in my Oracle. The date format from today's perspective should therefore look like this: 01-JAN-24

However, this year should adjust automatically as soon as we count the year 2024.

How can I do this?

Answer 1

One option is to truncate sysdate to year (and get 1st of January this year) and then add 12 months.

To get last day of the next year, add 24 months (2 years) and subtract one day:

SQL> select add_months(trunc(sysdate, 'yyyy'), 12) first_day,
  2         add_months(trunc(sysdate, 'yyyy'), 24) - 1 last_day
  3  from dual;

FIRST_DAY           LAST_DAY
------------------- -------------------
01.01.2024 00:00:00 31.12.2024 00:00:00

Answer 2

I think trunc(date) is the function you need:

select 
    trunc(sysdate, 'y') current_year
from 
    dual t1

This returns 1st January of current year.

select 
    add_months(trunc(sysdate, 'y'), 12) shifted_year
from 
    dual t1

now we just add 12 months to it