thing guy - 1 year ago 49

SQL Question

**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`

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 (

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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**