Prev: Update and locking
Next: When does a query/subquery return a NULL and when no value at all,
From: Frank Uray on 28 Apr 2010 09:16
Hi all

I a very strange effect when using different decimal settings
and calculating some values:

DECLARE @Decimal1 decimal(5, 2)
DECLARE @Decimal2 decimal(30, 2)

SET @Decimal1 = 0.2
SET @Decimal2 = 0.2

SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal1))
SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal2))

The first result is correct (0.00000000025832000),
the second is wrong, why ???

Thanks for any comments

Best regards
Frank Uray
From: jgurgul on 28 Apr 2010 09:32
Hi Frank

This is what happens when you multiple these precision&scales

Precision
15 + 30 = 45 + 1 = 46
Scale
2+15= 17

so (46,17)

Please see below articles:

http://msdn.microsoft.com/en-us/library/ms190476.aspx?ppud=4
http://blogs.msdn.com/sqlprogrammability/archive/2006/03/29/564110.aspx

* 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.

Jon

"Frank Uray" wrote:

> Hi all
>
> I a very strange effect when using different decimal settings
> and calculating some values:
>
> DECLARE @Decimal1 decimal(5, 2)
> DECLARE @Decimal2 decimal(30, 2)
>
> SET @Decimal1 = 0.2
> SET @Decimal2 = 0.2
>
> SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal1))
> SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal2))
>
> The first result is correct (0.00000000025832000),
> the second is wrong, why ???
>
> Thanks for any comments
>
> Best regards
> Frank Uray
 | 
Pages: 1
Prev: Update and locking
Next: When does a query/subquery return a NULL and when no value at all,