Oracle First Day of Next Quarter-CodePudding

Please help me find, based on any date within a given year, the next quarter. For example, any date in January through March will produce the first day of April in the same year. Any day in October through December will result in the first day of January the following year. In the following example, I am returning the numeric value of the quarter, but I want to replace the numeric value with the first day of the next quarter:

SELECT CASE
         WHEN TO_CHAR(sysdate, 'mm') IN (1, 2, 3) THEN
          1
         WHEN TO_CHAR(sysdate, 'mm') IN (4, 5, 6) THEN
          2
         WHEN TO_CHAR(sysdate, 'mm') IN (7, 8, 9) THEN
          3
         WHEN TO_CHAR(sysdate, 'mm') IN (10, 11, 12) THEN
          4
       END AS "Quarter"
  FROM dual

So, today's date is 12/21/2021, so I want to return 1/1/2022.

CodePudding user response：

select to_char(sysdate, 'q')              as quarter_number,
       trunc(sysdate, 'q')                as first_day_this_quarter,
       add_months(trunc(sysdate, 'q'), 3) as first_day_next_quarter
from   dual
;

QUARTER_NUMBER   FIRST_DAY_THIS_QUARTER   FIRST_DAY_NEXT_QUARTER
--------------   ----------------------   ----------------------
4                01-OCT-2021              01-JAN-2022

trunc() truncates to the date-time element given as the second argument (year, quarter, month, day, hour, minute, etc.); the default is "day", and q is for quarter.

add_months(), obviously, adds months.

CodePudding user response：

Looks like a little bit of date arithmetic.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test (datum) as
  2    (select date '2021-01-13' from dual union all
  3     select date '2021-05-23' from dual union all
  4     select date '2021-08-05' from dual union all
  5     select date '2021-11-11' from dual union all
  6     --
  7     select trunc(sysdate)    from dual            --> today
  8    )
  9  select datum,
 10    to_char(datum, 'q') quarter,
 11    add_months(trunc(datum, 'yyyy'), to_number(to_char(datum, 'q')) * 3) q_datum
 12  from test
 13  order by datum;

DATUM      Q Q_DATUM
---------- - ----------
13.01.2021 1 01.04.2021
23.05.2021 2 01.07.2021
05.08.2021 3 01.10.2021
11.11.2021 4 01.01.2022
22.12.2021 4 01.01.2022            --> today

SQL>