This discussion is archived
14 Replies Latest reply: Jan 28, 2013 3:57 AM by Frank Kulash RSS

Q in round year

Rahul_India Journeyer
Currently Being Moderated
How is Quarter format working?
with
test_date as
(select to_date(:a_date,'yyyymmddhh24miss') the_date 
          from dual
)

select the_date,'Q' pattern,round(the_date,'Q') rounded,trunc(the_date,'Q') truncated from test_date
  • 1. Re: Q in round year
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Rahul India wrote:
    How is Quarter format working?
    with
    test_date as
    (select to_date(:a_date,'yyyymmddhh24miss') the_date 
    from dual
    )
    
    select the_date,'Q' pattern,round(the_date,'Q') rounded,trunc(the_date,'Q') truncated from test_date
    <tt>ROUND (the_date, 'Q') </tt>     is the January 1, April 1, July 1 or October 1 that is closest to the_date.
    <tt>TRUNC (the_date, 'Q') </tt>     is the last January 1, April 1, July 1 or October 1 that is not later than the_date.

    Expermient. Display a whole buch of DATEs, with ROUND and TRUNC applied to each of them:
    WITH     got_dates      AS
    (
         SELECT     DATE '2012-11-01' + (LEVEL * 10)     AS the_date
         FROM     dual
         CONNECT BY     LEVEL <= 40
    )
    SELECT       the_date
    ,       ROUND (the_date, 'Q')     AS round_the_date
    ,       TRUNC (the_date, 'Q')     AS trunc_the_date
    FROM       got_dates
    ORDER BY  the_date
    ;
    Output:
    THE_DATE  ROUND_THE TRUNC_THE
    --------- --------- ---------
    11-NOV-12 01-OCT-12 01-OCT-12
    21-NOV-12 01-JAN-13 01-OCT-12
    01-DEC-12 01-JAN-13 01-OCT-12
    11-DEC-12 01-JAN-13 01-OCT-12
    21-DEC-12 01-JAN-13 01-OCT-12
    31-DEC-12 01-JAN-13 01-OCT-12
    10-JAN-13 01-JAN-13 01-JAN-13
    20-JAN-13 01-JAN-13 01-JAN-13
    30-JAN-13 01-JAN-13 01-JAN-13
    09-FEB-13 01-JAN-13 01-JAN-13
    19-FEB-13 01-APR-13 01-JAN-13
    01-MAR-13 01-APR-13 01-JAN-13
    11-MAR-13 01-APR-13 01-JAN-13
    21-MAR-13 01-APR-13 01-JAN-13
    31-MAR-13 01-APR-13 01-JAN-13
    10-APR-13 01-APR-13 01-APR-13
    20-APR-13 01-APR-13 01-APR-13
    30-APR-13 01-APR-13 01-APR-13
    10-MAY-13 01-APR-13 01-APR-13
    20-MAY-13 01-JUL-13 01-APR-13
    30-MAY-13 01-JUL-13 01-APR-13
    09-JUN-13 01-JUL-13 01-APR-13
    19-JUN-13 01-JUL-13 01-APR-13
    29-JUN-13 01-JUL-13 01-APR-13
    09-JUL-13 01-JUL-13 01-JUL-13
    19-JUL-13 01-JUL-13 01-JUL-13
    29-JUL-13 01-JUL-13 01-JUL-13
    08-AUG-13 01-JUL-13 01-JUL-13
    18-AUG-13 01-OCT-13 01-JUL-13
    28-AUG-13 01-OCT-13 01-JUL-13
    07-SEP-13 01-OCT-13 01-JUL-13
    17-SEP-13 01-OCT-13 01-JUL-13
    27-SEP-13 01-OCT-13 01-JUL-13
    07-OCT-13 01-OCT-13 01-OCT-13
    17-OCT-13 01-OCT-13 01-OCT-13
    27-OCT-13 01-OCT-13 01-OCT-13
    06-NOV-13 01-OCT-13 01-OCT-13
    16-NOV-13 01-JAN-14 01-OCT-13
    26-NOV-13 01-JAN-14 01-OCT-13
    06-DEC-13 01-JAN-14 01-OCT-13
  • 2. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    Frank
    Thanks. Got the concept.Do they ask round with dates in oca z-051?
  • 3. Re: Q in round year
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Rahul India wrote:
    Frank
    Thanks. Got the concept.Do they ask round with dates in oca z-051?
    Sorry, I don't know. What's oca z-051?

    Is that a certification exam? Post certification questions in the Certification.
  • 4. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    yes its an exam.
    ok
  • 5. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    <tt>ROUND (the_date, 'Q') </tt>     is the January 1, April 1, July 1 or October 1 that is closest to the_date.
    <tt>TRUNC (the_date, 'Q') </tt>     is the last January 1, April 1, July 1 or October 1 that is not later than the_date.
    Hi

    Why i am getting this?

    date | round date
    16-NOV-13 |     01-JAN-14
  • 6. Re: Q in round year
    Purvesh K Guru
    Currently Being Moderated
    Rahul India wrote:
    Why i am getting this?

    date | round date
    16-NOV-13 |     01-JAN-14
    Because, Q format rounds on 16th day of second month of quarter.

    Read the Format specifiers for Round function.

    Also, see this:
    with data as
    (
      select to_date('16-Nov-2013', 'DD-Mon-YYYY') dt from dual
    )
    select dt, round(dt, 'Q') round_dt, round(dt, 'Q') - dt diff_round, trunc(dt, 'Q') trunc_dt, dt - trunc(dt, 'Q') diff_trunc
      from data;
    
    DT                        ROUND_DT                  DIFF_ROUND             TRUNC_DT                  DIFF_TRUNC             
    ------------------------- ------------------------- ---------------------- ------------------------- ---------------------- 
    16-NOV-13                 01-JAN-14                 46                     01-OCT-13                 46 
    Begin equidistant from Both the Leading and lagging quarters, it picks the Leading quarter just because of the same reason as round(0.5) is considered as 1 rather than 0.
  • 7. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    Yes i figured it out.
    I took 31st Oct for counting the difference :p
  • 8. Re: Q in round year
    Purvesh K Guru
    Currently Being Moderated
    Glad it helped in your understanding.

    If this clears all your questions, then I will suggest you to Close the thread by marking it as Answered.
  • 9. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    Purvesh K wrote:
    Glad it helped in your understanding.

    If this clears all your questions, then I will suggest you to Close the thread by marking it as Answered.
    No i am still reading other formats.When i am done i will close the thread.

    DDD,DD,J return the current day.
    But when i am using them they are returning the same date.How to get the day,Using To_Char?
  • 10. Re: Q in round year
    Purvesh K Guru
    Currently Being Moderated
    Rahul India wrote:

    DDD,DD,J return the current day.
    But when i am using them they are returning the same date.How to get the day,Using To_Char?
    It works. Probably not at your end because of Time factor in your date column.

    See below:
    alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS';
    
    session set altered.
    
    
    with data as
    (
      select (sysdate+4/24) - 20 dt from dual
    )
    select dt, round(dt, 'DDD') rnd_ddd, round(dt, 'J') rnd_j
      from data;
    
    DT                        RND_DDD                   RND_J                     
    ------------------------- ------------------------- ------------------------- 
    08-Jan-2013 12:04:58      09-Jan-2013 00:00:00      09-Jan-2013 00:00:00
  • 11. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    You didnt got me.I mean if it is returning 01-jan-2013 then i should get the day like mon, tue etc for ddd for format
  • 12. Re: Q in round year
    Purvesh K Guru
    Currently Being Moderated
    Are you talking of this?
    with data as
    (
      select (sysdate+4/24) - 20 dt from dual
    )
    select dt, to_char(round(dt, 'DDD'), 'DY') rnd_ddd, round(dt, 'J') rnd_j
      from data;
    
    DT                        RND_DDD      RND_J                     
    ------------------------- ------------ ------------------------- 
    08-Jan-2013 12:11:06      WED          09-Jan-2013 00:00:00
  • 13. Re: Q in round year
    Rahul_India Journeyer
    Currently Being Moderated
    Yes.Thanks
  • 14. Re: Q in round year
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Rahul India wrote:
    <tt>ROUND (the_date, 'Q') </tt>     is the January 1, April 1, July 1 or October 1 that is closest to the_date.
    <tt>TRUNC (the_date, 'Q') </tt>     is the last January 1, April 1, July 1 or October 1 that is not later than the_date.
    Hi

    Why i am getting this?

    date | round date
    16-NOV-13 |     01-JAN-14
    When I said that ROUND returns the closest quarter-day, I oversimplified a little. As Purvesh said, it actually returns the previous quarter-day up to and including the 15th of February, May, August and November, and it returns the following quarter-day starting on the 16th of those months. Sometimes, that doesn't divide the quarters exactly in half.

    No matter where you set the cut-off point, you will always have a DATE that is exactly the same distance from 2 quarter-days, and you would need an arbiotrary rule to return either the earlier or the later day. Oracle chose to return the later. day

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points