April 13, 2004 at 12:16 pm
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
April 13, 2004 at 1:47 pm
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
April 13, 2004 at 1:55 pm
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]
April 13, 2004 at 1:57 pm
April 14, 2004 at 6:11 am
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
April 14, 2004 at 7:33 am
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
April 14, 2004 at 7:38 am
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]
April 14, 2004 at 5:22 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply