SQL Server calculation gives unexpected results

Question

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

Answer 1

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.