Difference between 7 and 7.00

Question

Difference between 7 and 7.00

karthik M

One Orange Chip

Points: 29139
More actions
April 27, 2009 at 8:37 pm

#199948

Hi All,
I have two set of statement.
select (((1+7.00/100)*(1+8.00/100)*(1+5.00/100))-1)*100
select (((1+7/100)*(1+8/100)*(1+5/100))-1)*100
if i execute the first one, it is giving
21.338000000000000000
if i execute the second one, it is giving
0
why? I think there is some difference between int and float calculation. But i am not sure...
Inputs are welcome!
karthik

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Check out BOL.

In a nutshell, "If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated."

So, to break down your second statement:

select (((1+7/100)*(1+8/100)*(1+5/100))-1)*100

1+7/100 = 1+0 = 1 (0.07, truncate the fraction = 0)

1+8/100 = 1+0 = 1 (0.08, truncate the fraction = 0)

1+5/100 = 1+0 = 1 (0.05, truncate the fraction = 0)

(1*1*1) - 1 = 1-1 = 0

0*100 = 0

When you use the 7.00, this forces it to use real numbers, ie. .07, .08, .05

Note you could also get the real numbers by dividing by 100.0

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

If you are using strictly column values, rather than variables, where you might not be able to enter 100.0, you can add *1.0 in the denominator to do the same thing.

select (((1+7/(100*1.0))*(1+8/(100*1.0))*(1+5/(100*1.0)))-1)*100

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.