thing guy thing guy - 1 year ago 60
SQL Question

Sql server gives different result for the same mathematical calculation

Step1

Query :

SELECT
(ISNULL(TRY_CONVERT(NUMERIC(38, 14), '123456789.34554563453524'), 0)
*
ISNULL(try_convert(NUMERIC(38, 14), '456789876.34556345345353'), 0))/100.0


Result :

563938115391720.660302


Step2

The above query I have modified to :

SELECT (123456789.34554563453524 * 456789876.34556345345353) / 100.0


But the result is :

563938115391720.660302253145411988


Here, the Step1 query result has truncated the decimals at some point.

How to re-write it to get the exact number (563938115391720.660302253145411988) I got for Step2 query.

gbn gbn
Answer Source

This is exactly as predicted by decimal precision. See T-SQL Decimal Division Accuracy

Numbers with decimal points are interpreted as NUMERIC in SQL Server to the exact precision shown. Not float. In this case decimal (23, 14)

In the first example they are decimal (38, 14)

Same result when using the same precision with explicit CONVERTs to the same datatype

SELECT
(ISNULL(TRY_CONVERT(NUMERIC(23, 14), '123456789.34554563453524'), 0) 
 * 
ISNULL(try_convert(NUMERIC(23, 14), '456789876.34556345345353'), 0))/100.0

563938115391720.660302253145411988

Note, the 100.0 is decimal(4,1) in this case too

Final words: this is long division that used to be taught at school after some precision changes because of the 38/23 difference in the multiplication

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download