Odd behavior SQL Server integer-type calculations

[ronspree]

Im using SQL Server 7.0 and have discovered this odd behavior:

A product is sold either as a whole unit or a fraction of it:
1 unit = 24 parts
and are represented by:
UnitQty smallint ' one whole unit
PartQty tinyint ' part of a unit, 1 unit = 24 parts
SellingPrice money ' price per unit
SalesAmount money ' Sales amount

The problem is in the calculation:
Select (UnitQty + PartQty/24) * SellingPrice as SalesAmount
Apparently, the term PartQty/24 is almost always evaluated to a zero because both the PartQty and the constant 24 are both of the integer data type. The workaround I did was:
Solution 1: PartQty / 24.0
Solution 2: Cast(PartQty as decimal) / 24
It works fine now. Has anyone encountered this odd behavior? What were your solutions?

Ron

 

[NoCoolHandle]

Ron,

I think you have found the soultion.. The numeric accuracy seems to be directly related to how many 00000's you put on the end of an int.. Often casting to a numeric can help. But if you just divide 4 by 3 SQL will round to the nearest integer (0)..


Rob