Rounding question

  • 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)

  • 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.

     

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply