March 30, 2012 at 3:39 am
Any body konws why their results are different? This issue has perplexed me for days.
--return 0.1655
declare @n1 decimal(16,4)
select @n1=CONVERT(money, 1234.5674288991)/CONVERT(money, 7456.5674288991)
select @n1
--return 0.1656
declare @n2 decimal(16,4)
select @n2=round(1234.5674288991,4)/round(7456.5674288991,4)
select @n2
March 30, 2012 at 4:00 am
Round Returns a numeric value, rounded to the specified length or precision.
March 30, 2012 at 5:40 am
The issue here is not the round, but the money data type. According to BOL, the money data type is precise to four decimal places. Unfortunately, this is only true for STORING data, not for calculations. What you have just discovered, is the rounding errors of the data type. If you do some calculations in a calculator, you will find that 1234.5674/7456.5674 = 0.1655677919574629, and since you divide two money values, the result is also money, causing the everything after the fourth decimal to be chopped off, and you get a value of 0.1655.
So, how do you avoid this? Easy, never money or smallmoney as data type.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply