3

Possible Duplicate:
T-SQL Decimal Division Accuracy

In SQL Server 2005, if I do the following query :

select  cast(1 as decimal(38,18))/cast(150 as decimal(38,18))

it returns 0.006666 (6 decimals)

however, if I do :

select  cast(1 as decimal(24,18))/cast(150 as decimal(24,18))

it returns 0.00666666666666 (14 decimals)

Could anyone please explain me those results ?

Community
  • 1
  • 1
user1185417
  • 99
  • 6
  • 2
    [The rules for result of division are explained here‌​.](http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx) `(38,18)/(38/18)` would have a result type of `(95,57)` according to the formula in BOL but gets truncated to `numeric(38,6)` and `(24,18)/(24/18)` would be `67,43` which only has 24 places to the left of the decimal so can be truncated to `38,14` – Martin Smith Nov 16 '12 at 15:39
  • @MartinSmith do you mean which only has 29 places instead of 24? So it will be(67-38 = 29) 67-29 = 38, 43-29 = 14, so it is 38,14. – András Ottó Nov 16 '12 at 16:49
  • @AndrásOttó - `67-43` is `24`. Where do you get 29 from? `numeric(67,43)` would be the resulting datatype of `(24,18)/(24/18)` ignoring that `38` is the absolute maximum then it gets reduced to `38` preserving the 24 integer places. – Martin Smith Nov 16 '12 at 16:51
  • @MartinSmith I read the linked thread from Aaron Bertrand, and based on that the calculation, because 67 is greater then 38, but this is the max for P, you have to trancate it. But P and S are linked, so if you truncate 67 to 38 (67-38 = 29), you have to truncate S (43-29 = 14), which results 38,14 as a result for the second calculation. Maybe I do not understand the original topics well, but it seems logical. – András Ottó Nov 16 '12 at 17:01
  • I think its the same thing. Just rearranging the equation. – Martin Smith Nov 16 '12 at 17:03
  • @MartinSmith yes, now I understand your way, so at the and you just used 38-24 = 14, which is 100% correct. That was the part what I missed first and did not understand, but yes you are rigth we both talk about the same. – András Ottó Nov 16 '12 at 17:06

0 Answers0