CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
haansi
haansi

Reputation: 5730

Why decimal behave differently?

I am doing this small exercise.

declare @No decimal(38,5);
set @No=12345678910111213.14151;

select @No*1000/1000,@No/1000*1000,@No;

Results are:

12345678910111213.141510    
12345678910111213.141000    
12345678910111213.14151

Why are the results of first 2 selects different when mathematically it should be same?

Upvotes: 0

Views: 227

Answers (7)

Anthony Faull
Anthony Faull

Reputation: 17957

You can get around the problem by using CONVERT or CAST on the first value in your expression to increase the number of decimal places and avoid a loss of precision.

DECLARE @num decimal(38,5)
SET @num = 12345678910111213.14151

SELECT CAST(@num AS decimal(38,8)) / 1000 * 1000

Upvotes: 0

KM.
KM.

Reputation: 103587

it is not going to do algebra to convert 1000/1000 to 1. it is going to actually follow the order of operations and do each step.

@No*1000/1000
 yields:  @No*1000  =  12345678910111213141.51000
          then /1000=  12345678910111213.141510

and 

    @No/1000*1000
yields:  @No/1000  = 12345678910111.213141
         then *1000= 12345678910111213.141000

by dividing first you lose decimal digits.

Upvotes: 2

Andrey
Andrey

Reputation: 60065

because when you divide first you get:

12345678910111.21314151

then only six decimal digits are left after point:

12345678910111.213141

then *1000

12345678910111213.141

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146499

because of rounding, the second sql first divides by 1000 which is 12345678910111.21314151, but your decimal is only 38,5, so you lose the last three decimal points.

Upvotes: 2

Crozin
Crozin

Reputation: 44376

It's probably because you lose part of data making division first. Notice that @No has 5-point decimal precision so when you divide this number by 1000 you suddenly need 8 digits for decimal part:

123.12345 / 1000 = 0.12312345

So the value has to be rounded (0.12312) and then this value is multiply by 1000 -> 123.12 (you lose 0.00345.

I think that's why the result is what it is...

Upvotes: 1

chris
chris

Reputation: 9993

because the intermediary type is the same as the argument's - in this case decimal(38,5). so dividing first gives you a loss of precision that's reflected in the truncated answer. multiplying by 1000 first doesn't give any loss of precision because that doesn't overload 38 digits.

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 185852

The first does @No*1000 then divides it by 1000. The intermediates values are always able to represent all the decimal places. The second expression first divides by 1000, which throws away the last two decimal places, before multiplying back to the original value.

Upvotes: 0

Related Questions