Using Monetary Data
Microsoft SQL Server 2005 stores monetary data, or currency values, using two data types: money and smallmoney. These data types can use any one of the following currency symbols.
.gif "Table of currency symbols, hexadecimal values")
Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value. For example, to assign a value of 100 Dollars to a variable, you can do the following:
DECLARE @dollars AS money
SET @dollars = $100
SELECT @dollars
The value returned is 100.0000, without a currency symbol.
If an object is defined as money, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.
money and smallmoney are limited to four decimal points. Use the decimal data type if more decimal points are required.
Use a period to separate partial monetary units, like cents, from whole monetary units. For example, 2.15 specifies 2 dollars and 15 cents.
Comma separators are not allowed in money or smallmoney constants, although the display format of these data types includes comma separators. You can specify the comma separators only in character strings explicitly cast to money or smallmoney. For example:
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
See Also
Other Resources
Data Types (Transact-SQL)
money and smallmoney (Transact-SQL)