Professional Web Applications Themes

SQL Rounding to 6 decimals instead of 8 - Microsoft SQL / MS SQL Server

Looks like a bug to me... I ran the script below on SQL 7.0 (I know, that's not SQL2000, maybe someone could try it there), and look at the surprising results... It started complaining exceeding the maximum precision when I raised the precision to 29. However, it does not seem to work correctly starting at precision 23. declare b2 decimal(24,8) declare NonNestingPercent decimal(24,8) set b2 = 0.017964 set NonNestingPercent = 0.12 select b2 * NonNestingPercent go declare b2 decimal(23,8) declare NonNestingPercent decimal(23,8) set b2 = 0.017964 set NonNestingPercent = 0.12 select b2 * NonNestingPercent go declare b2 decimal(22,8) declare NonNestingPercent ...

  1. #1

    Default Re: SQL Rounding to 6 decimals instead of 8

    Looks like a bug to me...

    I ran the script below on SQL 7.0 (I know, that's not SQL2000, maybe
    someone could try it there), and look at the surprising results... It
    started complaining exceeding the maximum precision when I raised the
    precision to 29. However, it does not seem to work correctly starting at
    precision 23.

    declare b2 decimal(24,8)
    declare NonNestingPercent decimal(24,8)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select b2 * NonNestingPercent
    go

    declare b2 decimal(23,8)
    declare NonNestingPercent decimal(23,8)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select b2 * NonNestingPercent
    go

    declare b2 decimal(22,8)
    declare NonNestingPercent decimal(22,8)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select b2 * NonNestingPercent
    go


    ----------------------------------------
    ..002156

    (1 row(s) affected)


    ----------------------------------------
    ..0021557

    (1 row(s) affected)


    ----------------------------------------
    ..002155680

    (1 row(s) affected)


    Gert-Jan
    Gert-Jan Guest

  2. #2

    Default Re: SQL Rounding to 6 decimals instead of 8

    Here are the SQL2K SP3 results:


    ..002156


    ..0021557


    ..002155680

    Looks the same as yours ...

    Gert-Jan Strik wrote: 


    Bob Guest

  3. #3

    Default Re: SQL Rounding to 6 decimals instead of 8

    Hmm,

    May be intermidiate calculation problems...

    I tried declaring the variables as decimal(24,10) then casting after the
    final result, and I got the following.


    --------------------------
    ..00215568

    (1 row(s) affected)


    --------------------------
    ..00215568

    (1 row(s) affected)


    --------------------------
    ..00215568

    (1 row(s) affected)

    Here's what I ran...

    declare b2 decimal(24,10)
    declare NonNestingPercent decimal(24,10)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select CAST(b2 * NonNestingPercent AS decimal(24,8))
    go

    declare b2 decimal(23,10)
    declare NonNestingPercent decimal(23,10)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select CAST(b2 * NonNestingPercent AS decimal(24,8))
    go

    declare b2 decimal(22,10)
    declare NonNestingPercent decimal(22,10)

    set b2 = 0.017964
    set NonNestingPercent = 0.12

    select CAST(b2 * NonNestingPercent AS decimal(24,8))
    go

    Dan C Douglas wrote:
     

    Abel Guest

  4. #4

    Default Re: SQL Rounding to 6 decimals instead of 8

    Thanks for the help..

    Strangly enough I changed the precision from 24 to 23 and I was able to get
    it to one additional decimal place, and changing it to 22 gave me 8
    decimals..

    I don't understand though as you saw in my results the one number that also
    had a 24 precision and 8 scale had well over 10 or 12 significant digits...

    Especially since:

    The default maximum precision of numeric and decimal data types is 38. In
    previous versions of SQL Server, the default maximum was 28.

    I am nowhere near the 38, nor the 28 max precision...


    "Gert-Jan Strik" <nl> wrote in message
    news:nl... 


    Dan Guest

  5. #5

    Default Re: SQL Rounding to 6 decimals instead of 8

    The reason for this behavior is that a numeric value can never have more
    than 38 digits.

    When you are multiplying two numeric(24,8) values, the database must select
    an appropriate temporary format to hold the result. To avoid possible
    overflow, the database always allocates as many digits as necessary to the
    left of the decimal point first.

    The selected format in this case is numeric(32,6)

    32 is the maximum number of digits to the left of the deicmal point after
    the multiplication (24-8)*2
    6 is the maximum number of decimals that can fit (38-32)

    Thus, when multiplying two numeric(24,8) numbers, you get the result as a
    numeric(32,6)

    To correct your problem you could use this:

    Cast((Cast(BallparkNumberOfBins as float) * Cast(NonNestingPercent as
    float)) as Decimal(24,8))

    This will perform the multiplication with full double-precision floating
    point precision, and then round the result to 8 decimals

    /SG



    Stefan Guest

  6. #6

    Default Re: SQL Rounding to 6 decimals instead of 8

    That makes sense... I assume you meant to say that numeric(24,8) *
    numeric(24,8) results in numeric(38,6) (instead of numeric(32,6)).

    In that case, it would also be a solution to cast to lower precision
    before multiplying. Of course, this requires the values to fit in 19
    digits.

    For example:
    Cast(BallparkNumberOfBins as numeric(19,8)) * Cast(NonNestingPercent as
    numeric(19,8))

    This would result in numeric(38,16)

    Gert-Jan

    Stefan Gustafsson wrote: 
    Gert-Jan Guest

Similar Threads

  1. decimals in InDesign
    By Lesley_Arenas@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 23
    Last Post: September 30th, 02:17 PM
  2. 14 decimals after comma
    By Dave_Saunders@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 29
    Last Post: September 30th, 01:09 PM
  3. MOD operator and decimals
    By Michael Hewlett in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: July 27th, 09:09 AM
  4. Decimals not Getting into ASP
    By msnews.microsoft.com in forum ASP Database
    Replies: 0
    Last Post: July 15th, 07:44 PM
  5. how to keep decimals from rounding
    By Rob Yelvington in forum PHP Development
    Replies: 3
    Last Post: August 26th, 04:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139