Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Money vs. Decimal Data type


Money vs. Decimal Data type

Author
Message
meichner
meichner
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 640
I am a c# program who uses SQL Server 2005 for my database. I had read a while back in another forum that using a Data Type of Money in my database may cause me some problems in my T-SQL code as well as my c# program. Is this true? I would appreciate answers/opinions to the following:

I need to stored prices in US dollars in my application. What is the best datatype to store these prices in my database and in my c# code?

Thanks
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11044 Visits: 14858
I think the money data type unless you are working with the federal debt.

The main issue is using currency types in an application as I believe they will use the region values set on the PC so it could $100 on a us PC and 100 euros on a french PC.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
meichner
meichner
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 640
Thanks. To be on the safe side do you see any problems using a decimal data type instead of money for US Dollars?

Thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45307 Visits: 39934
It depends... if you're not going to do anything to the values other than add or subtract... no problems. If you're going to do anything else (like multiply or divide), you'll need at least 4 decimal places to do the calculations without loosing overall accuracy.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
meichner
meichner
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 640
I was under the impression that one of the reasons that you use a decimal data type instead of a float is to avoid rounding issues. Does Money avoid rounding issues?

Thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45307 Visits: 39934
Most people think that FLOAT makes rounding issues... lemme as this... if you divide 1 by three and then multiply the answer times 3, what is the answer supposed to be? If you use DECIMAL(x,2), you probably won't get the answer you want.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jacob Luebbers
Jacob Luebbers
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1215
Jeff Moden (8/1/2008)
It depends... if you're not going to do anything to the values other than add or subtract... no problems. If you're going to do anything else (like multiply or divide), you'll need at least 4 decimal places to do the calculations without loosing overall accuracy.


The MONEY type has 4 decimal places - but the real answer depends on the degree of accuracy you need. Be careful about implicit conversions involving money if you need greater precision than 4 decimal places: http://tinyurl.com/59s2dn

There are also subtle performance differences between the two types - Aaron Betrand blogged about these (targeted at SQL Server 2008 but should hold fairly true for previous versions): http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/27/performance-storage-comparisons-money-vs-decimal.aspx

Regards,

Jacob
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45307 Visits: 39934
The big thing missing from Aaron's blog is the code he used for each test. Wink Didn't see much on accuracy, either...

I agree about the MONEY datatype, though... I'd just as soon it weren't available...


DECLARE @dOne DECIMAL(20,4),
@dThree DECIMAL(20,4),
@mOne MONEY,
@mThree MONEY,
@fOne FLOAT,
@fThree FLOAT

SELECT @dOne = 1,
@dThree = 3,
@mOne = 1,
@mThree = 3,
@fOne = 1,
@fThree = 3


SELECT @dOne/@dThree*@dThree AS DecimalResult,
@mOne/@mThree*@mThree AS MoneyResult,
@fOne/@fThree*@fThree AS FloatResult



--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
meichner
meichner
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 640
Thanks to both Jeff and Jacob for the help. Based on your posts I will use decimal instead of money. I was wondering, since I am only working with US dollars would DECIMAL(15,5) be a safe bet in order to avoid rounding issues?

Thanks

ps. At this point in my project my boss has not decided what we are going to do with the dollar amounts. We may just be adding and subtracting them, but I can't be sure that at some point we are not going to multiply or divide the dollar amounts.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45307 Visits: 39934
meichner (8/5/2008)
Thanks to both Jeff and Jacob for the help. Based on your posts I will use decimal instead of money. I was wondering, since I am only working with US dollars would DECIMAL(15,5) be a safe bet in order to avoid rounding issues?

Thanks

ps. At this point in my project my boss has not decided what we are going to do with the dollar amounts. We may just be adding and subtracting them, but I can't be sure that at some point we are not going to multiply or divide the dollar amounts.


Thanks for the feedback.

I'd have to say, "IT Depends" so far as the number of decimal places go. If you're doing long term mortgage caclulations, I'd be tempted to use something a bit more than 5 decimal places... you'll probably think I'm whacked, but I use FLOAT for money calculations (much more accurate than you think) and then round the answers for display... kinda like a calculator does.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search