CODEWITHSUNDEEP
sqlsql-server
CodeCook
CodeCook

Reputation: 188

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.

Upvotes: 1

Views: 327

Answers (1)

gbn
gbn

Reputation: 432271

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

Upvotes: 2

Related Questions