sqloracle-databasesql-date-functions

First day of the next year with rolling year in Oracle

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?

Solution

  • 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