CODEWITHSUNDEEP
sqlsql-servercastingrounding
floqui
floqui

Reputation: 165

Strange behavior while rounding in SQL server 2008

At some point I have a numeric(28,10) and I cast it in money (I know its bad but for legacy reason I have to return money) in the same time I also have to set the sign (multiplying by +1/-1).

In a first attempt I had cast the +/-1 to match the numeric type.

For the value 133.3481497944 we encounter a strange behavior (I have simplified the actual code in order to keep only the elements needed to demonstrate the problem):

SELECT CAST(CAST(133.3481497944 AS numeric(28,10))*cast(1 AS numeric(28,10)) AS money)

133.3482

which is not correctly rounded...

Removing the cast solve the problem

SELECT CAST(CAST(133.3481497944 AS numeric(28,10)) * 1 AS money)

133.3481

Did someone know what is happening in SQL? How can a multiplication by 1 and cast(1 AS numeric(28,10)) affect the result of the rounding?

Upvotes: 2

Views: 292

Answers (3)

GroundedTraveler
GroundedTraveler

Reputation: 15

SELECT 
CAST(CAST(133.3481497944 AS numeric(28,10))*cast(1 AS numeric(28,10)) AS money) --Your original,
CAST(1 AS numeric(28,10)) --Just the 1 casted,
CAST(133.3481497944 AS numeric(28,10)) --Your expected calculation,
CAST(133.3481497944 AS numeric(28,10))*cast(1 AS numeric(28,10)) -- The actual calculation

SELECT
CAST(133.3481497944 AS numeric(28,10))*cast(1.5 AS numeric(28,10)),
CAST(133.3481497944 AS numeric(28,10))*1.5,
CAST((133.3481497944*1) AS money),
133.3481497944*1

Returns

133.3482    
1.0000000000    
133.3481497944  
133.348150


200.022225  
200.02222469160


133.3481    
133.3481497944

So as mentioned above, there really isn't any true rounding, but a loss of precision during the cast. As to exactly why, I don't know. Most likely during the calculation(multiplication) while using the Numeric(28,10) it cuts off some precision.

I added the second lines to show that really you may not need your numeric casting.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

When multiplying numerics, SQL uses the following rules to determine the precision and scale of the output:

p = p1 + p2 + 1
s = s1 + s2

which makes sense - you wouldn't want 1.5 * 2.5 to be truncated to one digit past the decimal. Nor would you want 101 * 201 to be limited to 3 digits of precision, giving you 20300 instead of 20301.

In your case that would result in a precision of 57 and a scale of 20, which isn't possible - the maximum precision and scale is 38.

If the resulting type is too big, decimal digits are sacrificed in order to preserve the integral (most significant) part of the result.

From the SQL Programmability & API Development Team Blog:

In SQL Server 2005 RTM (and previous versions), we decided preserve a minimum scale of 6 in both multiplication and division.

So your answer depands on how big and precise you need the multiplier to be. In order to preserve 10 digits of decimal precision. If the multiplier needs a scale bigger than 9, then decimal digits may be truncated. If you use a smaller precision and scale, you should be fine:

SELECT CAST(CAST(133.3481497944 AS numeric(28,10))*cast(1 AS numeric(9,7)) AS money)

yields 133.3481.

Upvotes: 4

SQLMason
SQLMason

Reputation: 3275

I don't see any ROUNDing here. I only see casting. Don't assume that it will round, when you CAST. Historically, when we cast the environment truncates (SQL server or not) or behaves not as we expect - especially when we're talking about FLOATs.

Upvotes: 0

Related Questions