Monday, September 22, 2008

MSSQL Multiplication and Division Limitation

There was a case at work, the number is not precise enough for multiplication of data type decimal (38,10).

The result will give up to 6 decimal place precision and the rest 4 decimal place follows it will be truncated, replaced with 0.

Here's the sample in SQL Query Analyzer:

DECLARE @CrossRate as decimal(38,10)
DECLARE @ExchRate1 as decimal(38,10)
DECLARE @ExchRate2 as decimal(38,10)

SET @ExchRate1 = 1.3159643631
SET @ExchRate2 = 1.3663000000

SET @CrossRate = @ExchRate1/@ExchRate2
print '@ExchRate1: '
print @ExchRate1
print '@ExchRate2: '
print @ExchRate2
print '@CrossRate: '
print @CrossRate
And here's the result:
@ExchRate1:
1.3159643631
@ExchRate2:
1.3663000000
@CrossRate:
0.9631590000
However for data type decimal (18,10), multiplication result of the two data types will give the same precision - decimal(18,10).

The CrossRate result of decimal (18,10) is
0.9631591620

I am still not yet known how to solve this, as the requirement that we have is for data type decimal (38,10) instead of decimal (18,10). If you have any idea on how to solve this, please let me know. Thanks.

Some updates
My colleague just said that we can use float.
However there are some things to take note:

Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.

Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Converting from Decimal or Numeric to float can cause some loss of precision.

Reference: here

No comments: