Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Money vs. Decimal Data type Expand / Collapse
Author
Message
Posted Thursday, July 31, 2008 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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

Post #544518
Posted Thursday, July 31, 2008 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #544535
Posted Friday, August 1, 2008 5:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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
Post #545026
Posted Friday, August 1, 2008 9:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #545585
Posted Monday, August 4, 2008 6:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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
Post #546030
Posted Monday, August 4, 2008 5:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #546437
Posted Monday, August 4, 2008 11:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
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
Post #546514
Posted Monday, August 4, 2008 11:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
The big thing missing from Aaron's blog is the code he used for each test. ;) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #546521
Posted Tuesday, August 5, 2008 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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.
Post #546692
Posted Tuesday, August 5, 2008 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #547207
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse