Forum Stats

  • 3,839,341 Users
  • 2,262,485 Discussions
  • 7,900,938 Comments

Discussions

QTR date representation

Rengudi
Rengudi Member Posts: 568 Bronze Badge

Hi gurus,

Greetings .

I am working in oracle 11g R2 version.

I have request on representing qtrs using sql

SELECT TRUNC (TO_CHAR (dt, 'mm') / 3 - 0.1) qtr

  FROM (SELECT ADD_MONTHS (TRUNC (COLLECTION_DATE, 'y') - 1, ROWNUM) dt

          FROM table1

         WHERE ROWNUM <= 12)

the above query result is not right for my request.

I have collection date as date column, I want select qtr it belongs to. But, I have diff fiscal calendar

AUG,SEP,OCT => my Q1

NOV,DEC,JAN=> my Q2

FEB,MAR,APR=> my Q3

MAY,JUN,JUL=> my q4.

how get result like...

collection date    qtr

01-aug-16          q1

01-Jan-16           q2

30-Apr-16           q3

21-Jun-16           q4

Thanks

Raj

This discussion has been closed.