## Fun with Scale and Precision

 Author Message Stewart "Arturius" Campbell SSCrazy Eights Group: General Forum Members Points: 8887 Visits: 7281 All good and well, however, the explanation re why the rounding off takes place is.....? ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex” sql.selflearn Forum Newbie Group: General Forum Members Points: 3 Visits: 0 0.0001670000 vk-kirov Hall of Fame Group: General Forum Members Points: 3702 Visits: 4408 stewartc-708166 (12/3/2009)the explanation re why the rounding off takes place is.....?Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": http://technet.microsoft.com/en-us/library/ms190476.aspxWe have two numbers of type NUMERIC(38,10), so their precision = 38 and scale = 10.According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.But there is also a note:* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.Ok, the result precision (87) is definitely greater than 38, so it was reduced to 38. But why the scale was reduced to 6 – I can't find any explanation archie flockhart Ten Centuries Group: General Forum Members Points: 1320 Visits: 1154 INterestingly, and counter-intuitively, at least for me ...SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,10))gives this result: .000167While SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,1))gives this more accurate result.000167574 Colin Frame Old Hand Group: General Forum Members Points: 309 Visits: 342 The missing digits is due to the initial casts - with decimal, they are automatically converted to the minimum numeric precision and scale required before the calculation is done, so 1.67574 ends up with a scale of 5 and 10000 with a scale of 0. I would have thought this would lead to an initial result with scale of 5 i.e. 0.00016 and a final result of 0.0001600000 so I'm wondering how the 6th digit gets retained. Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. Colin Frame Old Hand Group: General Forum Members Points: 309 Visits: 342 This explains it:http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspxFor division:[EDITED - was incorrect, apologies CFF] scale of the result = max(6, s1 + p2 + 1)where s1 is the scale of the numerator and p2 is the precision of the denominator. Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. Dude76 Old Hand Group: General Forum Members Points: 300 Visits: 94 `declare @d1 decimal(38,10), @d2 decimal(38,10)SELECT @d1 = 1.67574, @d2 = 10000SELECT d1 = @d1, d2 = @d2, d1_div_d2 = @d1 / @d2, cast_div = CAST(@d1 / @d2 AS DECIMAL(38,10)), num_div = 1.67574 / 10000.0, cast_num_div = CAST(1.67574 / 10000.0 AS DECIMAL(38,10))`done (i trimed strings to results)`d1 d2 d1_div_d2 cast_div num_div cast_num_div------------ ---------------- --------- ------------ -------------- ------------1.6757400000 10000.0000000000 0.000167 0.0001670000 0.000167574000 0.0001675740`:-D My MCP Transcript (ID : 692471 Access : 109741229) nadabadan Old Hand Group: General Forum Members Points: 333 Visits: 1018 colin.frame (12/4/2009)This explains it:http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspxFor division: scale of the result = s1 + s2 + 1where s1 and s2 are the scales of the initial numbers.Nowhere is "s1+s2+1" mentioned in the link provided. mark.hammond SSC-Enthusiastic Group: General Forum Members Points: 148 Visits: 58 Odd that replacing the CAST(10000,NUMERIC(38,10)) with 10000 gives the more precise result 0.0001675740.Also odd that Sybase 15.0.2 gives the result 0.0001675740 with the original query. You'd think running the SAME query on two ANSI-standard DBMSs would give the SAME result. Carlo Romagnano SSCarpal Tunnel Group: General Forum Members Points: 4921 Visits: 3326 Explanation is in the note.Try to reduce the precision.SELECT CAST(cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) conv_factor returns 0.0001670000SELECT CAST(cast(1.67574 as decimal(29,10)) / cast(10000 as decimal(29,10)) AS DECIMAL(29,10) ) conv_factor returns 0.0001675740 I run on tuttopodismo