Post reply

Decimal Rounding - again...

  • July 24, 2007 at 9:37 am

    #372883

    Greetings all,

    Here comes the decimal thing again.

    This has stumped me and I am pretty sure it should not have.

    I have two decimals with 12 DPs each.

    If I run a simple divide between them, I get a nice long number with all the DPs.

    Example:

    SELECT 2874505.00680000000 / 191006947.283900000000 AS x

    My answer is 0.0150492170451136904 as expected.

    However, if these two values are in a table, the result is different.

    Col1 / Col2 = 0.015049 which you can see is heavily rounded.

    Oddly, putting the above select (SELECT 2874505.00680000000 / 191006947.283900000000 AS x) into a view, the resultant value is correct.

    Why would SQL, when given two values, divide them correctly yet when in a table, it rounds it to 6?

    The data type of the computed column is Decimal(38, 6) yes the view is 38, 19.

    This happens on both decimal and numeric. Exact same behavior.

    My initial reaction is I'm being stupid. Second was it's a bug, third, I'm being stupid.

    I cannot see it as a bug as I doubt I would have been the first to find it.

    Any ideas? Help?

    Thanks!

    DDL Below:

    CREATE TABLE decTest(

    Col1DECIMAL(38, 19),

    Col2DECIMAL(38, 19),

    Col3 AS Col1 / Col2

    )

    INSERT INTO DecTest VALUES(2874505.00680000000, 191006947.283900000000)

    SELECT *, Col1 / Col2 FROM DecTest

    --Above results do NOT match results below.

    SELECT 2874505.00680000000 / 191006947.283900000000 AS x

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 24, 2007 at 9:54 am

    #721976

    ok then, explained:

    http://msdn2.microsoft.com/en-us/library/ms190476.aspx

    With the help of a learned friend, it was pointed out.

    I guess initial reaction was correct

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

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

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