Money vs. Decimal Data type

Question

Money vs. Decimal Data type

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 1

Jeff Moden (8/6/2008)
Heh... provided that an inter rate, tax rate, discount rate, or any other rate is never used.

None of which are Money. All those rates will be Decimal, Numeric, or Float.

If you multiply 200 dollars/pounds/euros/yen by 10 dollars/pounds/euros/yen, the answer will be 2000... what? Dollars², pounds², etc? Multiply 200 dollars/pounds/euros/yen by a tax rate of 10% (0.1) and you get 2 dollars/pounds/euros/yen in tax. But the rate itself is not money.

Under what circumstances would you ever multiply an amount of money by another amount of money? I admit, I've done no accounting work, so maybe there are times when this is done. I just can't think of any at the moment.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jeff Moden (8/6/2008)
Heh... reminds me of the ol' hand assembly days... "Shift Left, Load Zeros". 😀

Wow - that one takes you back....:) That reminds me of having to use a hex editor to try to fix a screwed up FAT header (hoping against hope that the data is still there after a head crash). Those WERE some fun days.....:D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Tomm Carr (8/6/2008)
Jeff Moden (8/6/2008)
Heh... provided that an inter rate, tax rate, discount rate, or any other rate is never used.
None of which are Money. All those rates will be Decimal, Numeric, or Float.
If you multiply 200 dollars/pounds/euros/yen by 10 dollars/pounds/euros/yen, the answer will be 2000... what? Dollars², pounds², etc? Multiply 200 dollars/pounds/euros/yen by a tax rate of 10% (0.1) and you get 2 dollars/pounds/euros/yen in tax. But the rate itself is not money.
Under what circumstances would you ever multiply an amount of money by another amount of money? I admit, I've done no accounting work, so maybe there are times when this is done. I just can't think of any at the moment.

Yes... I absolutely agree, Tomm... you should never use the MONEY datatype for rates.

--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)

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 4

Gus has some very good points on this issue, that will be very helpful continuation for this post, for future readers.

http://www.sqlservercentral.com/Forums/FindPost807402.aspx

Jeff has given some good examples of why Float is often more accurate than decimal. Still, what is the main argument against Float for using in SQL Server tables? Float is officially an approximation datatype; I have not observed any discrepancies in use of Float datatype; But that is only me. Under what circumstance does Float do the approximations? Are there definite situations when it gets unpredictable?

Dan

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

repent_kog_is_near (10/23/2009)
Under what circumstance does Float do the approximations? Are there definite situations when it gets unpredictable?
Dan

They're done some fixes in SQL Server 2005 and up but let's take an example from SQL Server 2000...

DECLARE @Float1 FLOAT

DECLARE @Float2 FLOAT

SET @Float1 = 10

SET @Float2 = 1

SELECT @Float2/@Float1

That little slice of computational heaven used to produce the following in SQL Server 2000...

-----------------------------------------------------

0.10000000000000001

(1 row(s) affected)

Here's another one that still causes some concern even in 2k5... yeah... it's got a loop in it because it's 12 year old code and I've not taken the time to convert it because I only use it maybe once every 3 years as a demo...

SET NOCOUNT ON

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

Right around the .81 mark, you start getting stuff that you probably won't like...

----------------------

0.8

----------------------

0.810000000000001

----------------------

0.820000000000001

----------------------

0.830000000000001

----------------------

0.840000000000001

----------------------

0.850000000000001

----------------------

0.860000000000001

----------------------

0.870000000000001

----------------------

0.880000000000001

----------------------

0.890000000000001

----------------------

0.900000000000001

----------------------

0.910000000000001

----------------------

0.920000000000001

----------------------

0.930000000000001

----------------------

0.940000000000001

----------------------

0.950000000000001

----------------------

0.960000000000001

----------------------

0.970000000000001

----------------------

0.980000000000001

----------------------

0.990000000000001

----------------------

1

--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)

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 6

repent_kog_is_near

SSCertifiable

Points: 7278

October 23, 2009 at 7:56 pm

#1070321

Thanks Jeff

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

You bet. Thanks for the feedback.

Just to be clear and as Matt a couple of others alluded to, it's all a matter of precision and scale. Money is fine for addition, substraction all the time. So is a 2 place decimal. If you work with daily or even monthly interest rates, then you'll need to be a whole lot more precise. Depending on variances, FLOAT can be quite accurate despite it's apparent (binary based) approximation if you remember that even a 13 digit calculator processes at least 15 digits behind the scenes and then rounds the answer to 13 digits for display. The same should be done if you use FLOAT for money calculations. Of course, you could also use something like DECIMAL(38,28) and have a heck of a lot of accuracy that would resist the "tolerance buildup" of even a 30 year mortgage with daily interest calculations. Just keep in mind that many built in functions such as POWER return the FLOAT datatype...:Whistling:

Remember to store the most accurate data you need for whatever level of sophistication your sprocs require and save the rounding for the GUI to display.

"It Depends". 😉

--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)

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 8

Jeff

Let me try to summarize you:

*Bigger the precision (is it and/or scale), better the accuracy,especially in multiplication by a big number (Example: 2.45454545 (precision 9;scale 2) is more accurate than 2.5(precision 2,scale 1)

*Float will always be (more) accurate, but will have a small approximation (12.1000000000001 (15 precision) instead of 12.1 ). This is why it is called 'Non-Fixed point'

*The only way to get FLOAT kind of approximation is to get a bigger precision & Scale with the decimal type like DECIMAL(38,28), for example.

*Leave the rounding to GUI.

Numeric and money are called fixed-point. What exactly does this mean?

Can you please take a look at my example script in http://www.sqlservercentral.com/Forums/FindPost808655.aspx. Will you able to play with the precision/scale of the Decimal or Money variable and bring a more reasonable output? In that example, Float is more accurate than Decimal by 4billion. What if the data design is already set with decimal; how can we set it right to work with these kind of situations? Anything remotely possible?

Thanks for your insights

Dan

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

repent_kog_is_near (11/2/2009)
Jeff
Let me try to summarize you:
*Bigger the precision (is it and/or scale), better the accuracy,especially in multiplication by a big number (Example: 2.45454545 (precision 9;scale 2) is more accurate than 2.5(precision 2,scale 1)
*Float will always be (more) accurate, but will have a small approximation (12.1000000000001 (15 precision) instead of 12.1 ). This is why it is called 'Non-Fixed point'
*The only way to get FLOAT kind of approximation is to get a bigger precision & Scale with the decimal type like DECIMAL(38,28), for example.
*Leave the rounding to GUI.
Numeric and money are called fixed-point. What exactly does this mean?
Can you please take a look at my example script in http://www.sqlservercentral.com/Forums/FindPost808655.aspx. Will you able to play with the precision/scale of the Decimal or Money variable and bring a more reasonable output? In that example, Float is more accurate than Decimal by 4billion. What if the data design is already set with decimal; how can we set it right to work with these kind of situations? Anything remotely possible?
Thanks for your insights
Dan

No... again, it depends... FLOAT isn't always more accurate nor is DECIMAL. It all depends on what type of accuracy you really want. If you believe that 1/3*3 is 1, then FLOAT may be for you. If you believe that 1/3*3 is some number that will get very close to 1 but never reach 1, then DECIMAL may be right for you. And the type of rounding you intend to use for display purposes may also come into play. It all depends.

NUMERIC is NOT fixed point. It's a synonym for DECIMAL in SQL Server. In fact, if you do a simple SELECT/INTO tableB from tableA and tableA has DECIMAL columns, they will be converted to NUMERIC in SQL Server 2005 (ran into that today... GUI couldn't handle it, either).

Yes, I agree... leave the rounding to the GUI if you can tolerate it.

So far as the other post goes... why have you taken it upon yourslef to split the conversation by essentially double posting?

--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)

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 10

Hi

Float always gives the result of the calculator. That is why I assumed that Float will always be accurate. Can you give an example when Decimal gives better accuracy than float, in multiplication?

You said NUMERIC is NOT fixed point? Well, if Decimal is Fixed point, then Numberic is too, because they are synonyms as you said.

In your example, are you saying TableA had decimals, and SQL Server considers TableB columns as numeric?

Yes, I agree... leave the rounding to the GUI seems best.

My main reference to link the other post to here was in Post # http://www.sqlservercentral.com/Forums/FindPost548012.aspx where readers of this post can benefit from Gus's comments. I am stopping the double posting.

Thanks Jeff

Dan

DougGifford SSCommitted Points: 1815 More actions · Answer 11

I believe that a currency value should be stored as a 2 decimal value.

If you make a payment to someone, the payment is to 2 decimal places.

Your calculations can be made using any data type (Float, decimal 18, int, whatever) you want to use.

The final ROUNDED value should be in 2 decimal places.

Take for instance an excell spreadsheet column that has calculated enteries up to 6 decimal places but, the column is formatted to two decimal places.

If this column was a calculated check amount to be paid, the sum at the bottom of the column will be different than the total you would get if you were to add up the check amounts printed on each check. (Format the Column Total to 6 places then format to 2 decimal places. You will probably get two different Totals displayed.)

You can not write a check for $123.4567 but you can write a check for $123.46.

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

repent_kog_is_near (11/4/2009)
Hi
Float always gives the result of the calculator. That is why I assumed that Float will always be accurate. Can you give an example when Decimal gives better accuracy than float, in multiplication?

Sure (I think)... try loading a 17 digit whole number into FLOAT and then convert DECIMAL with 0 decimal places. It's a guess on my part... I can't verify it right now because I haven't reloaded SQL Server, yet, after a major wing-out on my desktop computer.

You said NUMERIC is NOT fixed point? Well, if Decimal is Fixed point, then Numberic is too, because they are synonyms as you said.

Ah... my bad. Not sure what I was thinking (I think I meant to say that Numeric is not floating point and fat fingered it) but, yes, Decimal and Numeric are fixed point and, except for the name, are identical within SQL Server. Externally, some GUI code will lose it's mind on one or the other.

In your example, are you saying TableA had decimals, and SQL Server considers TableB columns as numeric?

Not "considers".... is converted to.

My main reference to link the other post to here was in Post # http://www.sqlservercentral.com/Forums/FindPost548012.aspx where readers of this post can benefit from Gus's comments. I am stopping the double posting.

🙂

[/quote]

--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)

terrence 93071 SSC Rookie Points: 41 More actions · Answer 13

The rounding problem occurs after many operations, usually not in just few operations.

Try adding $0.01 to a total for 1000 times

DECLARE @Total FLOAT

DECLARE @i INT

SET @Total = 0

SET @i = 0

WHILE @i < 1000

BEGIN

SET @Total = @Total + 0.01

SET @i += 1

END

SELECT @Total

The result is

9.99999999999983

It may seem little but it will cause problems with comparisons, like it is suppose to be $10 but if you do a comparison, it will give you a result of less that $10.

If it is left alone, it will creep into other calculations, like interests (especially daily interest).

Depending on how frequent your mathematical operations are, it may give you problems any between 10 days to 10 years.

There is one way to get around the issue when using float.

Use cents, instead of dollars. So represent 1 dollar as 100 cents instead. Then you will not have any issue with adding 1 cent many times.

The fractional values below 1 cent is usually not a big deal for most people, but errors in 1 cent can become very significant.

Some financial markets even uses, half cent, quarter cent, 1/8 of a cent, which is perfect for binary representation.

So you decide what to use, but I would prefer to either use Money or Decimal or Float as cents.

Regards,

Terrence

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 14

You're supposed to round the final answer.

--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)