money and smallmoney (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Data types that represent monetary or currency values.
Remarks
Data type | Range | Storage |
---|---|---|
money | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. Informatica only supports two decimals, not four.) |
8 bytes |
smallmoney | -214,748.3648 to 214,748.3647 | 4 bytes |
The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent. For Informatica, the money and smallmoney data types are accurate to a one-hundredth of the monetary units that they represent.
Use a period to separate partial monetary units, like cents, from whole monetary units. For example, 2.15
specifies 2 dollars and 15 cents.
These data types can use any one of the following currency symbols.
Symbol | Currency | Hexadecimal value |
---|---|---|
$ | Dollar sign | 0024 |
¢ | Cent sign | 00A2 |
£ | Pound sign | 00A3 |
¤ | Currency sign | 00A4 |
¥ | Yen sign | 00A5 |
৲ | Bengali Rupee mark | 09F2 |
৳ | Bengali Rupee sign | 09F3 |
฿ | Thai Baht currency symbol | 0E3F |
៛ | Khmer Riel currency symbol | 17DB |
₠ | Euro currency sign | 20A0 |
₡ | Colon sign | 20A1 |
₢ | Cruzeiro sign | 20A2 |
₣ | French Franc sign | 20A3 |
₤ | Lira sign | 20A4 |
₥ | Mill sign | 20A5 |
₦ | Naira sign | 20A6 |
₧ | Peseta sign | 20A7 |
₨ | Rupee sign | 20A8 |
₩ | Won sign | 20A9 |
₪ | New Sheqel sign | 20AA |
₫ | Dong sign | 20AB |
€ | Euro sign | 20AC |
₭ | Kip sign | 20AD |
₮ | Tugrik sign | 20AE |
₯ | Drachma sign | 20AF |
₰ | German Penny sign | 20B0 |
₱ | Peso sign | 20B1 |
﷼ | Rial sign | FDFC |
﹩ | Small Dollar sign | FE69 |
$ | Full-width Dollar sign | FF04 |
¢ | Full-width Cent sign | FFE0 |
£ | Full-width Pound sign | FFE1 |
¥ | Full-width Yen sign | FFE5 |
₩ | Full-width Won sign | FFE6 |
You don't need to enclose currency or monetary data in single quotation marks ('
). While you can specify monetary values preceded by a currency symbol, SQL Server doesn't store any currency information associated with the symbol, it only stores the numeric value.
Warning
You can experience rounding errors through truncation, when storing monetary values as money and smallmoney. Avoid using this data type if your money or currency values are used in calculations. Instead, use the decimal data type with at least four decimal places.
Convert money data
When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4
is converted to the money equivalent of 4 monetary units.
The following example converts smallmoney and money values to varchar and decimal data types, respectively.
DECLARE @mymoney_sm SMALLMONEY = 3148.29,
@mymoney MONEY = 3148.29;
SELECT CAST(@mymoney_sm AS VARCHAR(20)) AS 'SM_MONEY VARCHAR(20)',
CAST(@mymoney AS DECIMAL) AS 'MONEY DECIMAL';
Here is the result set. Because the decimal type in the example doesn't have a scale, the value is truncated.
SM_MONEY VARCHAR(20) MONEY DECIMAL
------------------------------ ----------------------
3148.29 3148