4 Replies Latest reply: Jul 24, 2013 5:48 PM by thiago.gabriell RSS

    ROUND and TRUNC function

    thiago.gabriell

      Guys,

       

      About the query below, how is the output and how SQL treats that?

       

      SELECT TRUNC(ROUND(156,00,-1),-1)

      FROM DUAL

       

      Thank you!

        • 1. Re: ROUND and TRUNC function
          rp0428

          Don't be afraid of breaking Oracle by actually trying things.

           

          How is the output when you execute it?

           


          • 2. Re: ROUND and TRUNC function
            thiago.gabriell

            Hi rp0428,

             

            Let me correct it. I know the output is 160, but wants to understand how it's done. I read the oracle documentation and understand how ROUND and TRUNC works, but I can't understand the result.

             

            Thank you!

            • 3. Re: ROUND and TRUNC function
              rp0428

              Don't be afraid of breaking Oracle by actually trying things.

               

              Break it into pieces and look at each piece one at a time.

               

              Remove the TRUNC operation and see what you get. Do you understand why you get that result? If not, post it and we can help explain it.

              But you need to make the effort yourself.

               

              See the SQL Language doc. It has an example just like yours.

              http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions142.htm

              >

              ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to zero places. If integer is negative, then n is rounded off to the left of the decimal point.

               

              The following example rounds a number one digit to the left of the decimal point:

              SELECT ROUND(15.193,-1) "Round" FROM DUAL;    Round  ----------  20  

              >

              Which means your number, 156,00, will get rounded one digit to the left of the decimal point. So 156 gets rounded to 160.00 since the '6' is equal to or larger than '5' it gets rounded up.

               

              That leaves a result of 160.00. The TRUNC operation does something similar excepts instead of rounding one place left it truncates. But since one place left already has a '0' there is nothing for TRUNC to do so you still have 160.00

              • 4. Re: ROUND and TRUNC function
                Frank Kulash

                Hi,

                 

                 

                 

                eb816488-cca6-45a3-9b74-db582530b8a7 wrote:

                 

                Hi rp0428,

                 

                Let me correct it. I know the output is 160, but wants to understand how it's done. I read the oracle documentation and understand how ROUND and TRUNC works, but I can't understand the result.

                 

                Thank you!

                If you've read the manual, then you can ask a question like "Why does this query return 160?  I expected it to return ... because, in the section on ROUND, the manual says ... so I expect ROUND to return ... but when I run ROUND by itself, I get ...".  That way, you can get an answer that really focuses on what you need to know.