5

I think there was a function extract(quarter from date) in oracle sql to fetch the quarter for a given date. However the Oracle SQL 19c documentation here does not show the presence of quarter anymore.

I even tried running the code -

select extract(quarter from to_date('15/09/2021','DD/MM/YYYY')) as Q from dual;

The expected result is -

+---+
|  Q|
+---+
|  3|
+---+

Instead it gives an error

ORA-00907 missing right parenthesis

though it runs fine when quarter is replaced by month or year. What is the current way to fetch quarter given a date in Oracle SQL 19c?

Anirban Chakraborty
  • 539
  • 1
  • 5
  • 15
  • 2
    I doubt that "quarter" ever was an option for `extract`. Even though in principle there is no reason not to have it, I believe `extract` is based strictly on the internal data format Oracle uses for dates. That format has separate bytes for year, month, day, etc. - but not for quarter, or for week, or other calendar units. Those are all calculated, and are not options to `extract`. –  Sep 15 '21 at 17:52

1 Answers1

10

I will use sysdate for illustration - you can replace that with any expression of date data type.

The query returns a string (of a single character, a digit between 1 and 4); if you need a number value, wrap it within to_number().

select to_char(sysdate, 'Q') as qtr from  dual;

QTR
---
3