8 Replies Latest reply: Jan 26, 2013 8:15 AM by Etbin RSS

    Round and Trunc with dates

    Rahul_India
      Can i get the examples for Round and Trunc with dates?
        • 1. Re: Round and Trunc with dates
          Etbin
          Something to play with: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions255.htm#SQLRF52058

          Regards

          Etbin
          • 2. Re: Round and Trunc with dates
            Solomon Yakobson
            It would take you less time to read docs TRUNC(datetime).

            SY.
            • 3. Re: Round and Trunc with dates
              Rahul_India
              I need examples.i have read the doc already
              • 4. Re: Round and Trunc with dates
                ranit B
                Hi Rahul,

                Check out this workout by Tom Kyte on TRUNC and ROUND -
                http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sql-1735910.html

                Dont skip even a single line of it. Really useful.

                My little workout :
                Ranit>> select
                  2  round(123.444) r1,
                  3  round(123.999) r2,
                  4  trunc(123.444) t1,
                  5  trunc(123.999) t2
                  6  from
                  7  dual;
                
                        R1         R2         T1         T2                                                                                                                                                                                 
                ---------- ---------- ---------- ----------                                                                                                                                                                                 
                       123        124        123        123                                                                                                                                                                                 
                
                Ranit>> select
                  2  round(123.444,2) r1,
                  3  round(123.999,2) r2,
                  4  trunc(123.444,2) t1,
                  5  trunc(123.999,2) t2
                  6  from dual;
                
                        R1         R2         T1         T2                                                                                                                                                                                 
                ---------- ---------- ---------- ----------                                                                                                                                                                                 
                    123.44        124     123.44     123.99                                                                                                                                                                                 
                
                Ranit>> select
                  2  round(123.1234,2) r1, -- "Rounds upto 2 Decimal places"
                  3  round(123.1266,2) r2, -- "Rounds upto 2 Decimal places"
                  4  trunc(123.1234,2) t1, -- "Keeps only 2 decimal places and Truncates the rest"
                  5  trunc(123.1266,2) t2  -- "Keeps only 2 decimal places and Truncates the rest"
                  6  from
                  7  dual;
                
                        R1         R2         T1         T2                                                                                                                                                                                 
                ---------- ---------- ---------- ----------                                                                                                                                                                                 
                    123.12     123.13     123.12     123.12                                                                                                                                                                                 
                
                --"Note the difference in R2 andT2 values : In R2 there's a data round-off done but in T2 it is simple cut-off of extra decimal part"
                HTH

                Edited by: ranit B on Jan 26, 2013 7:24 PM
                • 5. Re: Round and Trunc with dates
                  Solomon Yakobson
                  ranit B wrote:
                  Dont skip even a single line of it. Really useful.
                  Question was on ROUND/TRUNC date, not number.

                  SY.
                  • 6. Re: Round and Trunc with dates
                    Rahul_India
                    Solomon Yakobson wrote:
                    ranit B wrote:
                    Dont skip even a single line of it. Really useful.
                    Question was on ROUND/TRUNC date, not number.

                    SY.
                    i am aware how they work with numbers
                    I need example with dates.
                    • 7. Re: Round and Trunc with dates
                      ranit B
                      Ohh my mistake!

                      @Rahul - But did you try doing some experiment yourself. It is pretty much the same.
                      Please tell us how much you tried and what made you stop?

                      I tried -
                      Ranit>> select
                        2  to_char(round(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),'dd-mon-yyyy hh:mi:ss am'),
                        3  to_char(trunc(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),'dd-mon-yyyy hh:mi:ss am')
                        4  from dual;
                      
                      TO_CHAR(ROUND(TO_DATE(' TO_CHAR(TRUNC(TO_DATE('                                                                                                                                                                             
                      ----------------------- -----------------------                                                                                                                                                                             
                      13-jan-2012 12:00:00 am 12-jan-2012 12:00:00 am                                                                                                                                                                             
                      
                      Ranit>> select
                        2  round(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),
                        3  trunc(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),
                        4  to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')
                        5  from dual;
                      
                      ROUND(TO_ TRUNC(TO_ TO_DATE('                                                                                                                                                                                               
                      --------- --------- ---------                                                                                                                                                                                               
                      13-JAN-12 12-JAN-12 12-JAN-12   
                      Edited by: ranit B on Jan 26, 2013 7:38 PM
                      • 8. Re: Round and Trunc with dates
                        Etbin
                        Like this ? ;)
                        with
                        test_date as
                        (select to_date(:a_date,'yyyymmddhh24miss') the_date 
                                  from dual
                        )
                        select the_date,'CC' pattern,round(the_date,'CC') rounded,trunc(the_date,'CC') truncated from test date
                        union all
                        select the_date,'SCC' pattern,round(the_date,'SCC') rounded,trunc(the_date,'SCC') truncated from test date
                        union all
                        select the_date,'SYYYY' pattern,round(the_date,'SYYYY') rounded,trunc(the_date,'SYYYY') truncated from test date
                        union all
                        select the_date,'YYYY' pattern,round(the_date,'YYYY') rounded,trunc(the_date,'YYYY') truncated from test date
                        union all
                        select the_date,'YEAR' pattern,round(the_date,'YEAR') rounded,trunc(the_date,'YEAR') truncated from test date
                        union all
                        select the_date,'SYEAR' pattern,round(the_date,'SYEAR') rounded,trunc(the_date,'SYEAR') truncated from test date
                        union all
                        select the_date,'YYY' pattern,round(the_date,'YYY') rounded,trunc(the_date,'YYY') truncated from test date
                        union all
                        select the_date,'YY' pattern,round(the_date,'YY') rounded,trunc(the_date,'YY') truncated from test date
                        union all
                        select the_date,'Y' pattern,round(the_date,'Y') rounded,trunc(the_date,'Y') truncated from test date
                        union all
                        select the_date,'IYYY' pattern,round(the_date,'IYYY') rounded,trunc(the_date,'IYYY') truncated from test date
                        union all
                        select the_date,'IYY' pattern,round(the_date,'IYY') rounded,trunc(the_date,'IYY') truncated from test date
                        union all
                        select the_date,'IY' pattern,round(the_date,'IY') rounded,trunc(the_date,'IY') truncated from test date
                        union all
                        select the_date,'I' pattern,round(the_date,'I') rounded,trunc(the_date,'I') truncated from test date
                        union all
                        select the_date,'Q' pattern,round(the_date,'Q') rounded,trunc(the_date,'Q') truncated from test date
                        union all
                        select the_date,'MONTH' pattern,round(the_date,'MONTH') rounded,trunc(the_date,'MONTH') truncated from test date
                        union all
                        select the_date,'MON' pattern,round(the_date,'MON') rounded,trunc(the_date,'MON') truncated from test date
                        union all
                        select the_date,'MM' pattern,round(the_date,'MM') rounded,trunc(the_date,'MM') truncated from test date
                        union all
                        select the_date,'RM' pattern,round(the_date,'RM') rounded,trunc(the_date,'RM') truncated from test date
                        union all
                        select the_date,'WW' pattern,round(the_date,'WW') rounded,trunc(the_date,'WW') truncated from test date
                        union all
                        select the_date,'IW' pattern,round(the_date,'IW') rounded,trunc(the_date,'IW') truncated from test date
                        union all
                        select the_date,'W' pattern,round(the_date,'W') rounded,trunc(the_date,'W') truncated from test date
                        union all
                        select the_date,'DDD' pattern,round(the_date,'DDD') rounded,trunc(the_date,'DDD') truncated from test date
                        union all
                        select the_date,'DD' pattern,round(the_date,'DD') rounded,trunc(the_date,'DD') truncated from test date
                        union all
                        select the_date,'J' pattern,round(the_date,'J') rounded,trunc(the_date,'J') truncated from test date
                        union all
                        select the_date,'DAY' pattern,round(the_date,'DAY') rounded,trunc(the_date,'DAY') truncated from test date
                        union all
                        select the_date,'DY' pattern,round(the_date,'DY') rounded,trunc(the_date,'DY') truncated from test date
                        union all
                        select the_date,'D' pattern,round(the_date,'D') rounded,trunc(the_date,'D') truncated from test date
                        union all
                        select the_date,'HH' pattern,round(the_date,'HH') rounded,trunc(the_date,'HH') truncated from test date
                        union all
                        select the_date,'HH12' pattern,round(the_date,'HH12') rounded,trunc(the_date,'HH12') truncated from test date
                        union all
                        select the_date,'HH24' pattern,round(the_date,'HH24') rounded,trunc(the_date,'HH24') truncated from test date
                        union all
                        select the_date,'MI' pattern,round(the_date,'MI') rounded,trunc(the_date,'MI') truncated from test date
                        Regards

                        Etbin