SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 What datatype is the best for monetary value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madlo
Starting Member

26 Posts

Posted - 02/21/2011 :  13:17:49  Show Profile  Reply with Quote
I am using SQL server 2008 R2.

I have a table with a column of datatype money.
I have a stored proc that receives a parameter of type money and inserts the value into the table.

If the user has no limit i store the max value.

Now here is my sql

exec User_Add '12345', 'John', 999999999999999.99

and it throws an error

Msg 8114, Level 16, State 5, Procedure User_Add, Line 0
Error converting data type numeric to money.


Now I change the value of the db and stored proc data type from money to numeric(17,2) and the sql command works.

I looked up money datatype for sql server 2008R2 and valid ranges are from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

The question is what is the valid range for money not up to 999,999,999,999.999.9999 and the reason is probably because of binary and saving space.

Money data type is not always the best size for money. Some currencies probably have more than 2 decimal points but on the other hand countries like Zimbabwe would require bigger number because of currency valuation.

What datatype and size is best practice for currency?

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/21/2011 :  14:41:33  Show Profile  Reply with Quote
You can try using the $ to denote a money datatype. For example:
DECLARE @T TABLE(Val MONEY)

INSERT @T (Val) VALUES ($922337203685477.5807)

As to what data type to actually use... It depends.. I use money becasue that's what it is. If performing a lot of calculations you can get math errors. But, those errors seem to be real edge case scenarios.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000