February 28, 2012 at 10:02 am
Hi,
I have a column that returns the quotient of 2 money fields. I've noticed that the results in this quotient field is always rounded to the 4th decimal even though I am not using the round() function. Has anybody experienced this before? If so, is there a way to force the result to give a more accurate answer than one that rounds to the 4th decimal place? I've tried forcing the quotient to round to a finer decimal place (ie. using "round([field1] / [field2], 10"). This will result in 10 decimals being displayed, but the last 6 are 0s
February 28, 2012 at 10:14 am
Money data type is working with 4 digits after the decimal point. If this is not enough for you, you should use a different data type.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 28, 2012 at 10:22 am
The money datatype can only be accurate to the ten-thousandth and does it's own rounding, which if you're using it as a data type for financial calcs you may wish to consider changing to a DECIMAL(19,4) or some other precision that best suits your needs. In your case, it's giving you zero's after the 4th decimal place because that's as far as the money datatype can go
Try this test using money versus decimal datatypes:
(Example taken from Stackoverflow)
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3
SELECT @mon4, @num4
Results:
Money ::2949.00
Decimal :: 2949.8525
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply