SqlDecimal inconsistencies with Decimal

  • Thread starter Thread starter and192_2003
  • Start date Start date
A

and192_2003

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
 
[email protected] said:
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.
Click to expand...

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
Click to expand...

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.
 
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
 
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?
Click to expand...

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
 
Back
Top