1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query To Display Quarter for given date

Discussion in 'General' started by jagadekara, Sep 13, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Query To Display Quarter for given date.....

    Code (SQL):
    SELECT sysdate
          ,(CASE WHEN to_char(given_date,'MON') IN ('JUN','JUL','AUG') THEN 'Q1'
                 WHEN to_char(given_date,'MON') IN ('SEP','OCT','NOV') THEN 'Q2'
                 WHEN to_char(given_date,'MON') IN ('DEC','JAN','FEB') THEN 'Q3'
                 WHEN to_char(given_date,'MON') IN ('MAR','APR','MAY') THEN 'Q4'  
          END) quarter
    FROM dual;
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Thanks Jagadekara.

    This will be useful for the countries (legislations) where the Oracle standard quarter can't be used (i.e. Jan-Mar=Q1):

    For ex.:

    Code (SQL):
    WITH months AS
    (SELECT add_months(trunc(sysdate, 'YYYY'), level-1) m
      FROM dual CONNECT BY level <= 12)
    SELECT m "Month", to_char(m, 'Q')  "Quarter" FROM months;

    MONTH             Quarter
    ------------------ ---------
    01-JAN-14          1
    01-FEB-14          1
    01-MAR-14          1
    01-APR-14          2
    01-MAY-14          2
    01-JUN-14          2
    01-JUL-14          3
    01-AUG-14          3
    01-SEP-14          3
    01-OCT-14          4
    01-NOV-14          4
    01-DEC-14          4