Problem in Decimal

Transact-SQL
1

Declare @a Decimal (38,20) = 131134.848
declare @b Decimal (38,20) = 2240000
select @a / @b

Result 0.058542
while the correct value is :0.05854234285714290

2

In this link, it is explained:

  1. The resulting scale is reduced to min(scale, 38 – (precision-scale)) if the integral part is less than 32, because it cannot be greater than 38 – (precision-scale). Result might be rounded in this case.
  2. The scale will not be changed if it is less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it cannot fit into decimal(38, scale)
  3. The scale will be set to 6 if it is greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or overflow error will be thrown if integral part cannot fit into 32 digits.

Especially part 3 gives the reason why you'd end up with 6 digits after decimal point.

3

declare @b Decimal (38,20) = 2240000 -- this is Quantity
Declare @a Decimal (38,20) = 131134.848 --- this is Value
Declare @c Decimal (38,20) = @a / @b

I want to spend this amount so that the value is 0
Taking into account that when drainage through the equation
@b * @c

4

Have you read and understood the section I posted?

If you declare your variables as decimal(32,20) you'd probably get what you want. decimal(32,20) can still hold a might big number/amount.

5

I understand

Declare @a Decimal (32,20) = 131134.848
declare @b Decimal (32,20) = 2240000
DECLARE @C DECIMAL (32,20)
SET @C = @a / @b
SELECT (@C * @b) - @a -- I need Result = 0

6

Then don't use Decimal (38,20) or CAST it in some way such that the calculation rules THEN suit your problem / purpose.

Do you really need numbers up to a trillion with 20 decimal places (its entirely possible that you do, just seems like a rare requirement ...)

7

I really hate that SQL Server does this... it makes Granny's 4 function calculator look great by comparison.

8

Because of the lousy MS rules that @bitsmed was good enough to post, you can avoid most forms of rounding hell by being careful not to scale your inputs to what you expect for an output. For example, the following provides an even more correct answer that what you provided as the "correct" answer.

DECLARE @a DECIMAL(19,3) = 131134.848
DECLARE @b DECIMAL(19,3) = 2240000
 SELECT @a / @b

0.0585423428571428571 --Result from above
0.05854234285714290 -- What you posted as the "correct" answer, which still isn't rounded correctly.