CODEWITHSUNDEEP
sqlsql-server
user3514987
user3514987

Reputation: 220

SQL Server calculation gives unexpected results

I have a very weird scenario which i am sure the is an explanation for.

I trying to calculate something and here is the values:

 SELECT 145.28/63 *(8 * 100) / 100 -- returns: 18.4482480000

The problem is that this is not correct, If i do the calculation with Windows Calculator then it returns a more precise value:

145,28/63 *(8 - 0 * 100) / 100 = 18,44825396825397

I had to change a lot to get it work with SQL Server:

SELECT 145.28  * 100 * 8 / (100 * 63) --RETURNS: 18.4482539682539

How come i need to change the numbers in SQL Server but not in windows?

In Navision programming it returns the correct value which is 18.4482539682539682

Upvotes: 3

Views: 195

Answers (4)

Alireza
Alireza

Reputation: 5056

The result's precision is calculated based on some factors that may give you a scale lower than what you expect. Just try this one:

SELECT CONVERT(DECIMAL(38, 35), 145.28)/63 *(8 * 100)

which will give you

1844.825396825396825396825397

Upvotes: 1

Tristan
Tristan

Reputation: 1024

the default datatype that is automatically assigned simply doesnt hold the precision you want. This can easily be fixed by explicitly casting your first number as a more precise datatype like this:

SELECT CAST(145.28 AS decimal(38,35))/63 *(8 * 100) / 100 
--returns 18.448253968253968253968253

From there on out sql server understand not to downcast it so the result becomes 18.448253968253968253968253

syntax for decimal is decimal(total positions,of which after comma). don't forget that the comma takes a place as well.

Here is a link for docu.

If you want to limit the decimals you can use ROUND like this:

SELECT ROUND(CAST(145.28 AS float)/63 *(8 * 100) / 100,12)

The maximum precision you can achieve in sql server (as a single value anyway) is 18.448253968253968253968253

per linked docu from ms:

decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

Upvotes: 8

Maco
Maco

Reputation: 429

The issue is with precision. When you do 145.28/63 in Sql it evaluates to 2.306031. If you do it in windows calculator it evaluates to 2.3060317460317460317460317460317. So your multiplying different numbers thus the difference in the result.

To get the same result as in Navision try using a decimal with really high precision:

SELECT cast(145.28 as decimal(35,30))/63 *(8 * 100) / 100

It evalueates to 18.4482539682539682

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

Here Your execution steps are creating this difference. You can try this for achiving the same result.

SELECT 145.28 *(8 * 100)/63 / 100;

Which will resulted as 18.4482539600.

Upvotes: -1

Related Questions