Problem with getting the quarter from a date in Oracle

Question

I've written a query to get the start date of the quarters from current year and previous year by using the sysdate.

eg. Today falls in the 1st quarter of the year, therefore I only want to get the start date of 1st quarter of last year and this year. If I'm on December (which is in the 4th quarter), I want to get the start dates of 8 quarters (4 from last year, 4 from this year.)

select b.dt,
       to_number(to_char(SYSDATE, 'Q')),
       to_number(to_char(b.dt, 'Q'))
  from dual a,
       (select add_months(trunc(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12),
                                'yyyy'),
                          (rownum - 1) * 3) dt
          from all_objects
         where rownum <= 8
           and add_months(trunc(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12),
                                'yyyy'),
                          (rownum - 1) * 3) <= SYSDATE
           and to_number(to_char(SYSDATE, 'Q')) >=
               to_number(to_char(add_months(trunc(ADD_MONTHS(TRUNC(SYSDATE,
                                                                   'MM'),
                                                             -12),
                                                  'yyyy'),
                                            (rownum - 1) * 3),
                                 'Q'))) b

This query only returns the start date of 1st quarter of last year. I expect to get the start date of the 1st quarter of this year as well.

Littlefoot Littlefoot · Accepted Answer · 2019-03-24T05:47:43

Here's one option; see comments within the code.

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

Session altered.

SQL> with
  2  -- this mimics SYSDATE
  3  today (datum) as
  4    (select date '&date_literal' from dual),
  5  -- which quarter does DATUM belong to? Find 1st day in "this" and "previous" year
  6  quart as
  7    (select trunc(datum, 'yyyy') this,
  8            trunc(add_months(datum, -12), 'yyyy') previous,
  9            to_char(datum, 'q') quart from today)
 10  -- the fina result
 11  select add_months(this, (level - 1) * 3) result
 12  from quart
 13  connect by level <= quart
 14  union all
 15  select add_months(previous, (level - 1) * 3) result
 16  from quart
 17  connect by level <= quart;
Enter value for date_literal: 2019-03-24

RESULT
----------
01.01.2019
01.01.2018

SQL> /
Enter value for date_literal: 2019-08-13

RESULT
----------
01.01.2019
01.04.2019
01.07.2019
01.01.2018
01.04.2018
01.07.2018

6 rows selected.

SQL>

Problem with getting the quarter from a date in Oracle

1 Answers