14 Replies Latest reply: Jan 28, 2013 5:57 AM by Frank Kulash RSS

    Q in round year

    Rahul_India
      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
          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
            Frank
            Thanks. Got the concept.Do they ask round with dates in oca z-051?
            • 3. Re: Q in round year
              Frank Kulash
              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
                yes its an exam.
                ok
                • 5. Re: Q in round year
                  Rahul_India
                  <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
                    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
                      Yes i figured it out.
                      I took 31st Oct for counting the difference :p
                      • 8. Re: Q in round year
                        Purvesh K
                        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
                          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
                            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
                              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
                                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
                                  Yes.Thanks
                                  • 14. Re: Q in round year
                                    Frank Kulash
                                    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