Don't Be Fooled By SQL Zero Values

Have you ever done a straight SQL division and found that the answer is always zero despite the fact that you just KNOW the two values being divided? If you didn't already know this there is a little trick to ensure this doesn't happen. For example the following code will result in a zero:

DECLARE @VARDATEFROM DateTime, @VARDATETO DATETIME, @VarRate real, @InterestAmount as real, @ReturnRate as real, @NumDays as int--, @InterestAmount as real

--assign stuff here

select @InterestAmount = ((@NumDays/365) * @VarRate) * @InvAmount

select @InterestAmount

But just making a little change to the values by multiplying by 1.0 will give you the result you need:

DECLARE @VARDATEFROM DateTime, @VARDATETO DATETIME, @VarRate real, @InterestAmount as real, @ReturnRate as real, @NumDays as int--, @InterestAmount as real

--assign stuff here

select @InterestAmount = (((@NumDays * 1.0)/(365 * 1.0)) * @VarRate) * @InvAmount

select @InterestAmount

And @InterestAmount will now give a result. A little tip should you encounter this highly annoying problem!