Show me the [small]money!

by Michael S. Kaplan, published on 2005/07/22 03:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/07/22/441633.aspx

SQL Server's currency data types have some interesting international features. And some of the intricacies of those features have some interesting international implications. I figured as long as we were here I could talk about some of them....

Both numbers are pretty much scaled integer types rather than floating point values (the latter would freak out a lot of people when it comes to money, so it makes sense to build the types this way). Though of course if you need more than four decimal places it is recommended that use the Decimal data type (there are apparently currencies for whom it is recommended to store more than four decimal places to help with complex calculations).

You also cannot include currency grouping separators (commas for en-US) unless you pass the value as a string -- in which case you will want to be sure that the currency grouping and decimal separators all match the language of the session you are in. I usually like to put in straight numbers and not worry about dependencies on the language settings, myself.

But the really interesting information is in the topic entitled Using Monetary Data. What this datatype allows is any currency symbol to be put in front of the number, even if it is not in a string (enclosed by single quotes) in a Transact-SQL clause. Basically, all of the following currency signs are supported:

Codepoint	Symbol	Name
U+0024	$	DOLLAR SIGN
U+00a3	£	POUND SIGN
U+00a4	¤	CURRENCY SIGN
U+00a5	¥	YEN SIGN
U+09f2	৲	BENGALI RUPEE MARK
U+09f3	৳	BENGALI RUPEE SIGN
U+0e3f	฿	THAI CURRENCY SYMBOL BAHT
U+20a1	₡	COLON SIGN
U+20a2	₢	CRUZEIRO SIGN
U+20a3	₣	FRENCH FRANC SIGN
U+20a4	₤	LIRA SIGN
U+20a6	₦	NAIRA SIGN
U+20a7	₧	PESETA SIGN
U+20a8	₨	RUPEE SIGN
U+20a9	₩	WON SIGN
U+20aa	₪	NEW SHEQEL SIGN
U+20ab	₫	DONG SIGN
U+20ac	€	EURO SIGN

As a bit of trivia, if you look at the Using Monetary Data topic, it has the same table as above, sorted in code point order, with one exception: the Euro (U+20ac) is placed just before U+20a1. The reason for this is that once upon a time (in the original Books Online topic that shipped with the initial release of SQL Server 2000), the documentation listed U+20a0 as the euro.

Now the code in SQL Server did not do this (since that was not really the euro), and if you tried to use U+20a0 (₠, a.k.a. EURO-CURRENCY SIGN) as a currency sign in a money or smallmoney column, it would not work.

When they finally fixed the documentation, it was I suppose easier to update the table by updating the two entries without moving stuff around in the table....

Interestingly. I just looked in SQL Server 2005 Books Online and this table has not been updated there, either to add new entries or to fix that one ordering issue. Oops. But that is kind of minor, no sense worrying about that....

Now for the real problems -- you knew there would be real problems, didn't you? :-)

There are 22 characters in the Currency Symbols block (only 11 of which SQL Server recognizes in this case). Most importantly, there are 41 characters in the Sc (Symbol, Currency) general category (only 18 of which SQL Server recognizes in this case). For both of these you can look to the links to see the list of currency symbols....

Or alternately, it would be cool if they removed some of the items that no longer really exist since they have converted to the euro now, and maybe added some more in.

However, I will now take a step back and not ask for those features just yet....

Note that you can just insert your currency values with any of these currency symbols in front of them. And the values will be inserted. As Is. Which may not be what you want if you deal with €100 vs. ¥100 vs. ₩100 for example (since €100 is about ¥13,541 or ₩126,178 by today's fix!).

Now that currency symbol is not stored, either -- the currency's identity is eliminated after the insert. Fill in your own disaster sequence on this one -- and make sure to be careful of what you insert in your application....

Now I have worked with the Cloanto Currency Server several times, and would highly recommend them to people who would want to deal with different types of currencies and do conversions. It is pretty cool having the results at your fingertips and available through both automation and .NET, too. Very cool stuff (I have been a loyal user since I first tried it back in 1999 while I was writing my book -- there is even a sample on the book's CD!).

Anyway, the best practice for SQL Server is just keep the money and smallmoney columns with a single currency, or store the currency type in another field. It will keep you from doing something you did not intend to do with the database. Big mistakes (where big is defined as scope of effect; the actual mistake is usually a small design issue) in these sorts of columns are the surest way to find oneself looking for another job....

Hi,

Very interesting article!

Just one small side comment, the word "euro" is not capitalised in English nor in any language except German... :-)

You have to talk about "the euro" and not "the Euro".

Daniel

If you actually need to deal with different currencies in your app, the notion of exchange rates will invariably raise its ugly head. However, it is too much to expect that SQL Server will calculate exchange rates for you.

It is reasonable to expect that SQL Server just leave the data that you give it alone. So I prefer the expected behavior. I am willing to demand that developers design their tables accordingly:

CREATE TABLE BankTransaction
(
TransactionID numeric PRIMARY KEY IDENTITY,
Date datetime DEFAULT GETDATE(),
Amount money, -- just numbers, thanks
Currency nchar -- or maybe nchar(50)?
-- perhaps a USDEquivalent field, populated at INSERT time?
)

And furthermore

CREATE TABLE ExchangeRates
(
AsOf datetime,
CurrencyFrom nchar,
CurrencyTo nchar,
Rate float
-- this field populated on a scheduled basis
)

In my mind it is just a little scary that there is not even an example on how to save that currency symbol to know what to convert to? Or info on the topic at all?

The current implementation has an incomplete list of currency symbols that it will cheerfully accept and then throw away without storing any indication of what it was.... :-(

In my experience this is the best way:
- extra column for currency
- use ISO 4217
(http://en.wikipedia.org/wiki/ISO_4217)

A symbol is not good enough ($ symbol is used for many other dollars, not only the U.S. one, like Argentina, Australia, Brunei, Canada, Chile, Columbia, Ecuador, El Salvador, Mexico, New Zealand, Singapore).

Plus, having a locale independent identified (3 char ISO code) allows you to show localized names. Examples for USD: $, US$, "dollar des États-Unis", "Доллар США" etc.

Link to the Unicode CLDR database (Common Locale Data Repository):
http://www.unicode.org/cldr/
ftp://ftp.unicode.org/Public/cldr

An article about this (no SQL, but still some valid points):
http://www.mihainita.net/20050709a.shtml

Do you see anything wrong with this approach?

Hey Maurits --

But cast('¢69' as money) works just fine. They seem quite adamant that the currency sign must be in front.

And of course

select
cast('$124' as money) + cast('¢69' as money)

will return 193.00....

Oops!

Mihai -- the approach you suggest is solid, and it is the ISO4217 codes that Cloanto uses for its conversions, FWIW. But SQL Server does not know about them, and neither Windows nor the .NET Framework parse or format with them either, unfortunately.

I guess that would be an interesting feature to suggest for future versions of Windows and the .NET Framework, for both parsing and formatting. Though the issues for parsing are not trivial since the cross-locale question has to be answered...

"Though the issues for parsing are not trivial since the cross-locale question has to be answered..."

Yes, it is a tough one. And I don't have a good solution for it. It is in the same class with parsing 123.456 = no clue what this is without a locale.

But it might be better than ShowThisAsCurrency(1000) and getting $1000 or ¥1000 depending on the system. Right now, no one gets the output right (Win, Mac, Java, ...)
Except for ICU (was not there in 2.8, is there in 3.2, now sure when it was introduced).

That dollar sign (US and Canada) is incorrect.

The correct symbol is a S with a U overlayed on it. (i.e. US on top of eachother and no, it wasn't a mistake or just a conicidence)

I know, not the point of the post, but... All fonts should have this the right way instead of the killed way (MS keyboards have it wrong too!)

I found missing symbol.
U+005c
This is another Yen sign.

I can do a following query.
select convert(money, nchar(0x5c) + '1')

This symbol lack in latest SQL Server 2005 books online.

Regards

Since the path separator is not officially a currency symbol ( but only looks like one on JPN/KOR), it is thre only for compatibility. I will blog about this soon....

Hello, Michael.

Thank you for your quick response.
I create a symbol compare table between SQL Server 2000 and 2005.

http://blogs.sqlpassj.org/yoshihirokawabata/archive/2005/10/11/14516.aspx

Regards,

Please consider a donation to keep this archive running, maintained and free of advertising.
Donate €20 or more to receive an offline copy of the whole archive including all images.