Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
madlo
Starting Member
41 Posts |
 Posted - 2011-02-21 : 13:17:49
|
| 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 sqlexec User_Add '12345', 'John', 999999999999999.99and it throws an errorMsg 8114, Level 16, State 5, Procedure User_Add, Line 0Error 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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-21 : 14:41:33
|
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. |
 |
|
|
|
|
|
|
|
What datatype is the best for monetary value