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.

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