In SQL Server 2000 and 2005 you have the money data type and the smallmoney data type. (Oracle doesn’t have this data type)
You wouldn’t think there is anything smaller than a penny or a cent in money matters, so why did they designed these data types with 4 decimal places? To help hackers puts all the partial pennies in a separate bank account? :-p or to keep compatibility with Excel and allow easier data import from Excel spreadsheets?
Apparently, the only reason is to let developers insert currency symbols that won’t be preserved:
USE tempdb;
GO
CREATE TABLE TestMoney (cola INT PRIMARY KEY, colb MONEY);
GO
SET NOCOUNT ON;
GO
-- The following three INSERT statements work.
INSERT INTO TestMoney VALUES (1, $123.45);
GO
INSERT INTO TestMoney VALUES (2, $123123.45);
GO
INSERT INTO TestMoney VALUES (3, CAST('$444,123.45' AS MONEY) );
GO
-- This INSERT statement gets an error because of the comma
-- separator in the money string.
INSERT INTO TestMoney VALUES (3, $555,123.45);
GO
SET NOCOUNT OFF;
GO
SELECT * FROM TestMoney;
GO
DECLARE @dollars AS money |
The value returned is 100.0000, without a currency symbol.
If you’re a developer, these data types will make you round your values to two decimal places before retrieving them to your UI and they will also give you a couple of headaches with calculated data.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92830
Stay away from the money type, use decimal instead.
To add more to the causes of why they should deprecate this data type.
If your UI is written in .NET and you want to present only 2 decimal places, you would probably use the Math.Round function. This function uses the bankers rounding, which will round to the second place after the decimal point depending on whether this number is odd or even, doing a ceiling round if the number is odd and a floor round if the number is even whenever the third decimal place is 5.
See more here
http://en.wikipedia.org/wiki/Rounding#Round-to-even_method
On the other hand, T-SQL Round function does not implement the banker’s rounding. If you use T_SQL to generate your reports, be aware that the data showing on your report won’t be the same as the data showing on your .NET UI…
Stay away from this gotcha!