Best data type for currency?

Question

Best data type for currency?

J Bagwell

SSC-Addicted

Points: 404
More actions
April 13, 2004 at 12:16 pm

#86561

I'm developing a database in SQL Server 2000 and I need to store some financial data, all in US Dollars. Is it best to use the SQL Server data type 'money' for currency or to use a 'float' or 'decimal'? I seem to have some very vague memory of some drawbacks to using the built in data type, but I may be wrong. Or it may have been a previous version or even a different product. Any advice?
Thanks,
Jana
J. Bagwell
UVA Health System

Viewing 8 posts - 1 through 7 (of 7 total)

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

fhanlon SSChampion Points: 13131 More actions · Answer 1

I would use the money type. But then I would be probably using .net which supports the money type via its currency data type. If you are using C++ or Java via TDS or JDBC you may want to check to ensure that the money type can be mapped to something the application code can read.

Francis

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Don't use float. It's imprecise by nature.

I would use decimal as this is an ANSI standard datatype.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

J Bagwell SSC-Addicted Points: 404 More actions · Answer 3

I'm using Access to produce a report off of this data at the moment. I'm hoping to move to using Reporting Services next month. The data seems to show up fine in Access using the money type. Mine is mostly a reporting app and will not have a data entry front end.

J. Bagwell

UVA Health System

Jeff Burton SSCommitted Points: 1550 More actions · Answer 4

The "money" datatype is ok for U.S. money, but it allows 4 decimal places not 2 for cents! Your app should prevent those hundredths/thousandths from being stored if you decide to use it; otherwise use decimal (38,2).

Jeff

fhanlon SSChampion Points: 13131 More actions · Answer 5

If you use the money type you should not prevent the hundredths/thousandths from being stored. These decimal places are for arithmetic precision. If you need to multiply/divide your money you may need these decimal places to ensure no loss of significant data.

Francis

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

AFAIK, not quite right!

You store with only two decimal places, but you calculate with at least 5 or 6.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SmithDM SSC Eights! Points: 859 More actions · Answer 7

Having worked with this problem before (import of access "money" type into SQL2k) you have a slight problem that is not always obvious. Even though Access says money and appears to only have two decimals in Access, when brought over to SQL2k as money there can be non-zero values in decimal position 3 and 4 (it appears Access is actually using float). You can not directly load to or process as 2 decimals in SQL2k. A straight move truncates decimals 3 and 4. In order for SQL2k to report the same totals as Access you need to ROUND the Money field to two decimals. To save space I normally use Decimal(14,2) for Dollar only fields. Unless you are the US government you are never going to get to trillions of dollars. The other reason to use Decimal is for printouts. If you use money type you alway have to truncate field to 2 decimals on output for user consumpion (again with possible rounding problem if you did not fix on input).

SmithDM