Rounding question

• SQL_Surfer

  SSCrazy Eights

  Points: 8316

  More actions

  June 15, 2021 at 8:43 pm

  #3896702

  If i execute query 1, i get 0.00036369128107, but if i execute query 2, I get 0.00000000000000. What i want it is I want the results (i.e non zero) but with 6 decimal places. How can i accomplish this? So, I'm looking for something like 0.000363  or 0.000364.

  Any idea how to accomplish this?

  1. select 44293.92/ 121789886.93

  2. select ROUND(44293.92/121789886.93,3)

• Mr. Brian Gale

  SSC-Insane

  Points: 24687

  More actions

  June 15, 2021 at 9:59 pm

  #3896719

  With your ROUND, you are rounding to 3 decimal places so that is why you are getting a bunch of 0's.

  So your options are:

  1 - change your ROUND to be rounding to 6 decimal places by replacing your "3" at the end with a "6"

  2 - CAST it to a different data type than what I am expecting is FLOAT or possibly REAL

  If you go with option 2, I recommend using the NUMERIC datatype as then you can specify the number of digits past the decimal point.

  An example of both approaches:

  SELECT ROUND(44293.92/ 121789886.93,6)
  SELECT CAST(44293.92/ 121789886.93 AS NUMERIC(19,6))

  Approach number 1 gives you up to 19 digits with 6 of those being in the decimal place section.  It will round it in your case to 0.000364 as is expected when rounding.

  Approach number 2 gives you 6 digits past the decimal and then 0's out the remaining, but this is fine as 0.5 is EXACTLY the same number as 0.50 or 0.500000000000.  Those are all the same value.

   

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• Jeffrey Williams

  SSC Guru

  Points: 90249

  More actions

  June 16, 2021 at 2:41 pm

  #3897014

  Both ROUND and CAST to a numeric will round the number up or down - the difference will be the value returned.  Using CAST will return 6 digits to the right of the decimal where using ROUND will return 0's out to the size of the calculated value.

   Select 46293.92 / 121789886.93
        , round(46293.92 / 121789886.93, 6)
        , cast(46293.92 / 121789886.93 As numeric(18,6));
  

  This returns the following (value rounded down / truncated):

  

   Select 45293.92 / 121789886.93
        , round(45293.92 / 121789886.93, 6)
        , cast(45293.92 / 121789886.93 As numeric(18,6));

  This one returns the following (rounded up):

  

   

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs