SqlDecimal inconsistencies with Decimal

Hello there!

We have found some rather odd behaviour in the SqlDecimal type, which
is not displayed in the
standard Decimal type.

It appears that mathematical methods choose a precision and scale that
is inappropriate for the result. Not only
is the result truncated to 6 dp, but the rounding is also incorrect.
This does not occur in Decimal.

Can anyone either tell me what I am doing wrong, or point me in the
direction of a workaround or fix. We can't use
Decimal since our requiments are for a SQL standard of accuracy in our
mathematical operations.

I have enclosed some VB to replicate it and the output from the
program.

Code:
Imports System.Data.SqlTypes
Module Module1
Sub Main()

Dim sLeftSide, sRightSide, sResult As SqlDecimal
Dim dLeftSide, dRightSide, dResult As Decimal

sLeftSide = SqlDecimal.Parse("6252")
sRightSide = SqlDecimal.Parse("342")

sResult = SqlDecimal.Divide(sLeftSide, sRightSide)

dLeftSide = 6252
dRightSide = 342

sResult = SqlDecimal.Divide(sLeftSide, sRightSide)
Console.WriteLine("SqlDecimal Result: " + sResult.ToString)

dResult = Decimal.Divide(dLeftSide, dRightSide)
Console.WriteLine("Decimal Result: " + dResult.ToString)

End Sub
End Module

Output:
SqlDecimal Result: 18.280701
Decimal Result: 18.280701754385964912280701754

Andy

Larry
Thu Nov 10 08:20:59 CST 2005


and192_2...@hotmail.com wrote:
> Hello there!
>
> We have found some rather odd behaviour in the SqlDecimal type, which
> is not displayed in the
> standard Decimal type.
>
> It appears that mathematical methods choose a precision and scale that
> is inappropriate for the result. Not only
> is the result truncated to 6 dp, but the rounding is also incorrect.
> This does not occur in Decimal.
>
> Can anyone either tell me what I am doing wrong, or point me in the
> direction of a workaround or fix. We can't use
> Decimal since our requiments are for a SQL standard of accuracy in our
> mathematical operations.

Is SqlDecimal more precise than Decimal? Note my below workaround won't
accept any more trailing 0s (throws a FormatException on the .Parse)

>
> I have enclosed some VB to replicate it and the output from the
> program.
>
> Code:
> Imports System.Data.SqlTypes
> Module Module1
> Sub Main()
>
> Dim sLeftSide, sRightSide, sResult As SqlDecimal
> Dim dLeftSide, dRightSide, dResult As Decimal
>
> sLeftSide = SqlDecimal.Parse("6252")
> sRightSide = SqlDecimal.Parse("342")
>
> sResult = SqlDecimal.Divide(sLeftSide, sRightSide)
>
> dLeftSide = 6252
> dRightSide = 342
>
> sResult = SqlDecimal.Divide(sLeftSide, sRightSide)
> Console.WriteLine("SqlDecimal Result: " + sResult.ToString)
>
> dResult = Decimal.Divide(dLeftSide, dRightSide)
> Console.WriteLine("Decimal Result: " + dResult.ToString)
>
> End Sub
> End Module
>
> Output:
> SqlDecimal Result: 18.280701
> Decimal Result: 18.280701754385964912280701754

I don't know why it picks 6 decimal places by default, but have a look
at this:

?sqldecimal.divide(sqldecimal.parse("6252"),sqldecimal.parse("342")).value
18.280701D
?sqldecimal.divide(sqldecimal.parse("6252.0"),sqldecimal.parse("342")).value
18.280701D
?sqldecimal.divide(sqldecimal.parse("6252.00"),sqldecimal.parse("342")).value
18.280701D
?sqldecimal.divide(sqldecimal.parse("6252.000"),sqldecimal.parse("342")).value
18.2807017D
?sqldecimal.divide(sqldecimal.parse("6252.0000"),sqldecimal.parse("342")).value
18.28070175D
(etc)
?sqldecimal.divide(sqldecimal.parse("6252.00000000000000000000000"),sqldecimal.parse("342")).value
18.280701754385964912280701754D

Crazy world isn't it.

--
Larry Lard
Replies to group please

and192_2003
Thu Nov 10 09:43:51 CST 2005

Thankyou for your reply.

Yes, SqlDecimal allows a precision of 38, whereas Decimal is (i think)
29.

We have witnessed the behaviour you show in that the precision and
scale of the result is determined from the precision and scale of the
input. This is ok for multiply, but for divide surely the result's
dimensions should be the maximum SqlDecimal can hold, and then control
over rounding be given to the coder as in Decimal? Interestingly the
following T-SQL code works ok? do MS not use SqlDecimal internally?

declare @leftSide as decimal
declare @rightSide as decimal

set @leftSide = 6252
set @rightSide = 342

print @leftSide / @rightSide

Damien
Fri Nov 11 02:06:57 CST 2005


and192_2003@hotmail.com wrote:
> Thankyou for your reply.
>
> Yes, SqlDecimal allows a precision of 38, whereas Decimal is (i think)
> 29.
>
> We have witnessed the behaviour you show in that the precision and
> scale of the result is determined from the precision and scale of the
> input. This is ok for multiply, but for divide surely the result's
> dimensions should be the maximum SqlDecimal can hold, and then control
> over rounding be given to the coder as in Decimal? Interestingly the
> following T-SQL code works ok? do MS not use SqlDecimal internally?
>

SqlDecimal is a data type within the .NET framework. It should be
compatible with decimal datatypes in any ADO.NET data provider. T-SQLs
decimal datatype is a native datatype within SQL Server. It has existed
since before the .NET framework. So, no, MS do not use SqlDecimal
internally.

Damien