Datatype for calculations
-
I'm going to be doing calculations (addition and division) on data (sales figures in dollars) and am wondering what is the best datatype to use for my tables. The sales figures will have decimial place.
Thank you.
-
I'm going to use FLOAT. I've found out that doing division on MONEY gives incorrect results.
-
Read up on float before you decide. Float is an "approximate" data type and not always the best choice for the manipulation of real money. I'd use numeric and specify the expected scale and precision.
And then again, I might be wrong ...
David Webb -
I had a table with values for goals (stored with datatype [money]) and was dividing the sum of the values in other tables (stored with datatype [money]) by the value of the goal.
Using [money] for the goal table value datatype, the results would be incorrect. When I changed it to [float] the calculations work correctly.
-
Using float for financial values is a bad idea. It's an inaccurate data-type and you may well get rounding errors.
Use either numeric or decimal and set the scale high enough that you get the required accuracy. If you need your calculations accurate to 4 decimal places, use a numeric with a scale of 5 or 6
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I sure would like to know why you think MONEY gives incorrect results... 4 decimal places isn't enough? Maybe not for interest calcs and monthly payment calcs...
But David and Gail are absolutely correct about FLOAT (and REAL)... if you don't think so, try this simple experiment... and I haven't even touched on divide, yet...
DECLARE @Penny FLOAT
SET @Penny = .01
DECLARE @Money FLOAT
SET @Money = 0
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 1000
BEGIN
SET @Money = @Money + @Penny
SELECT @Money
SET @Counter = @Counter + 1
END
If you want the accuracy of a scientific or business calculator, use DECIMAL with 15 decimal places and round to only 13 decimal places just like they do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
...forgot the divide example... you want THIS kind of crappy accuracy?
Tell me which company you're writing this for so I can make sure I don't have any stock in it, please 
DECLARE @Float1 FLOAT
DECLARE @Float2 FLOAT
SET @Float1 = 10
SET @Float2 = 1
SELECT @Float2/@Float1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply