CODEWITHSUNDEEP
sqlsql-servermathdecimaldivision
Faraday
Faraday

Reputation: 2954

Divide decimal by decimal and get zero

Why when I do:

select CAST(1 AS DECIMAL(38, 28))/CAST(1625625 AS DECIMAL(38, 28))

Do I get 0, but when I do 

select CAST(1 AS DECIMAL(20, 10))/CAST(1625625 AS DECIMAL(20, 10))

I get a value?

I would have expected the higher precision division to return a higher precision result, why is this not the case?

Upvotes: 3

Views: 204

Answers (2)

user275683
user275683

Reputation:

As an alternative you can always do this instead

SELECT CONVERT(DECIMAL(38,28), 1 / 1625625.0)

SELECT CONVERT(DECIMAL(10,10), 1 / 1625625.0)

SELECT CONVERT(DECIMAL(38,28), 1.0 / 1625625)

SELECT CONVERT(DECIMAL(10,10), 1.0 / 1625625)

All 4 queries will lead to same result. if you don't put .0 on one of two parts it will treat as integer division and only result will be converted to decimal.

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196002

This happens because for math operations the resulting precision and scale is calculated with the rules found in http://msdn.microsoft.com/en-us/library/ms190476.aspx#division

operation
e1 / e2

result precision
p1 - s1 + s2 + max(6, s1 + p2 + 1)

result scale
max(6, s1 + p2 + 1)

So when using decimal(38/28) for both parts then you end with decimal(105,67) which is not supported so it get truncated.

Also quoting

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.

Upvotes: 3

Related Questions