Muokkaa

Jaa

money and smallmoney (Transact-SQL)

  • Artikkeli

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's 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

Related content