Check out this simple query

SELECT 0.172 * (365 / (90 + 91)) * 1.26 AS test1

, (0.172 * 365) / (90 + 91) * 1.26 AS test2

, 0.172 / ((90 + 91) / 365) * 1.26 AS test3

test1 and test2 should give the same result, but they don't ! test1 gives wrong result; test 2 gives correct result. Furthermore, test3 gives a Divide by Zero

This makes no sense.

(365 / (90 + 91)) is a constant I use many times in my code; only way to procede is to replace it by a number, or have i misunderstood something

I am running the query on SQL Server 8.0.2040



Re: Simple expression fails. Must be a bug?

Sankar Reddy

just commenting on the third resultset. divide by zero makes perfect sense and its correct.

By carefully putting the braces around ((90+91)/365), this part will be evaluated first, resulting to (181/365), translates to '0'. Thats why its throwing the divide by zero error.

Re: Simple expression fails. Must be a bug?

Hugh Qu - MSFT

Without decimal point, numbers 90, 91, and 365 are treated as datatype "int". And division of two ints also produces int, by returning the module only. Therefore the problem.

If you append a decimal point to all the three numbers, you would get what you expected.

Re: Simple expression fails. Must be a bug?


SQL does what it can to preserve data types. When you use (365 / (90 + 91)) SQL assumes that you want the result to be an Integer, becuase you specified all integers. Consider this:

Code Snippet

select 90/91, 90.0/91.0

You'll see that though the numbers are equal, SQL will evaluate them very differently.

In your example, the differences are because of the combinations of decimal places and parenth-placement.

Adding a simple decimal point without any following characters is probably enough to get "close enough."

You can read a lot about "significant digits." I used a sort of rule of thumb: if you want accuracy to two decimal places, then supply two decimal places: 90.00 / 91.00

Code Snippet

select 90. / 90, 90/ 90., 90.0 / 91.0, 90. / 91., 90.00 / 91.00

Re: Simple expression fails. Must be a bug?


Thank you all. I will put decimal points on the figures. HMM...