Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 30, 2016 - 11:24 pm UTC

Last updated: October 01, 2016 - 12:27 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I need to write a statement that selects the quarter of a date as a text string like this: First Quarter.
This is what I have now, but how do I rerewrite this(code below) to display the outcome as stated in the previous sentence.
SELECT TO_CHAR(TO_DATE('01-FEB-2002'),'Q')
FROM DUAL;

and Connor said...

I was sure there was a format mask to do this, but I cant remember it :-) so someone might be able to post it.

But if there isnt, a simple decode will do

SQL> SELECT
  2  decode(
  3    TO_CHAR(TO_DATE('01-FEB-2002'),'Q'),
  4    '1','FIRST',
  5    '2','SECOND',
  6    '3','THIRD',
  7    '4','FOURTH') qtr
  8  FROM DUAL;

QTR
-----
FIRST

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Add SP and TH

Stew Ashton, October 01, 2016 - 8:08 am UTC 

alter session set nls_date_format='yyyy-mm-dd';
with dates as (
  select add_months(trunc(sysdate,'YY'), level-1) dte from dual
  connect by level <= 12
)
select dte, to_char(dte, 'Q') Q, to_char(dte, 'QSP') qsp,
  to_char(dte, 'QTH') qth, to_char(dte, 'QSPTH') qspth
from dates;

DTE        Q QSP    QTH  QSPTH
2016-01-01 1 ONE    1ST  FIRST
2016-02-01 1 ONE    1ST  FIRST
2016-03-01 1 ONE    1ST  FIRST
2016-04-01 2 TWO    2ND  SECOND
2016-05-01 2 TWO    2ND  SECOND
2016-06-01 2 TWO    2ND  SECOND
2016-07-01 3 THREE  3RD  THIRD
2016-08-01 3 THREE  3RD  THIRD
2016-09-01 3 THREE  3RD  THIRD
2016-10-01 4 FOUR   4TH  FOURTH
2016-11-01 4 FOUR   4TH  FOURTH
2016-12-01 4 FOUR   4TH  FOURTH

Connor McDonald
October 01, 2016 - 12:27 pm UTC

Thanks Stew - I *knew* there was a mask for it :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.