It is really strange how auto convert between numeric data behaves in T-Sql
Declare @fdays as float(12)
Declare @mAmount As Money
Declare @fDaysi as float(12)
Set @fdays =3
Set @fdaysi =1
Set @mAmount=527228.52
Set @mAmount = @fdaysi * @mAmount/@fDays
Select @mAmount, 527228.52/3
The result of this computation is
175742.8281 175742.840000
Does this occur because money and float are not actually the same kind of numeric data? Float is Approximate Numeric and Money is Exact Numeric
Money and Decimal are fixed numeric datatypes while Float is an approximate numeric datatype. Results of mathematical operations on floating point numbers can seem unpredictable, especially when rounding is involved. Be sure you understand the significance of the difference before you use Float!
Also, Money doesn't provide any advantages over Decimal. If fractional units up to 5 decimal places are not valid in your currency or database schema, just use Decimal with the appropriate precision and scale.
ref link : http://www.sqlservercentral.com/Forums/Topic1408159-391-1.aspx
Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?
float [ (n) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.
When n in 1-24 then precision is 7 digits. When n in 25-53 then precision is 15 digits.
So in your example precision is 7 digits, thus first part @fdaysi * @mAmount
rounds result to 7 digits 527228.5. The second part returns 527228.5/3=175742.828 and casting 175742.828 to Money results in 175742.8281. So FLOAT and REAL are approximate data types and sometimes you get such surprises.
DECLARE @f AS FLOAT = '29545428.022495';
SELECT CAST(@f AS NUMERIC(28, 14)) AS value;
The result of this is 29545428.02249500200000 with just a casting.
