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....

The money and smallmoney topic in MSDN gives the basics of the datatypes:

money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through
2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

smallmoney

Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.

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....

I would be a lot happier if SQL Server were looking for a return of UnicodeCategory.CurrencySymbol from CharUnicodeInfo.GetUnicodeCategory or some other convenient way of getting the currency symbols and treating them that way, of course.

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....

 

This post brought to you by "" (U+20a0, a.k.a. EURO-CURRENCY SIGN)


# Daniel García on 22 Jul 2005 6:24 AM:

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

# Michael S. Kaplan on 22 Jul 2005 8:54 AM:

Hi Daniel,

Ok, fixed those. I am only an occasional user of them, so I do not know all the rules. :-)

# Maurits [MSFT] on 22 Jul 2005 2:45 PM:

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
)

# Michael S. Kaplan on 22 Jul 2005 2:54 PM:

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.... :-(

# Mihai on 22 Jul 2005 3:15 PM:

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?

# Maurits [MSFT] on 22 Jul 2005 3:19 PM:

select
cast('$124' as money), -- this works
cast('69¢' as money) -- this doesn't

# Michael S. Kaplan on 22 Jul 2005 3:36 PM:

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!

# Michael S. Kaplan on 22 Jul 2005 3:56 PM:

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...

# Mihai on 22 Jul 2005 4:19 PM:

"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).

# James Hancock on 23 Jul 2005 12:08 AM:

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!)

# Dean Harding on 24 Jul 2005 7:20 AM:

> The correct symbol is a S with a U overlayed on it.

That's not really the popular theory. See: http://www.ex.ac.uk/~RDavies/arian/dollar.html for example.

# Yoshihiro kawabata on 11 Oct 2005 6:54 AM:

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

# Michael S. Kaplan on 11 Oct 2005 7:16 AM:

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....

# Yoshihiro kawabata on 11 Oct 2005 7:43 AM:

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,

referenced by

2010/12/14 Falling back shouldn't mean falling over (though perhaps it does, a bit)

2008/07/05 Show me the money? How?

2007/11/09 If you get a yen for something a bit wider you might be out of luck

2007/03/28 A yen for Yen may be left unsatiated

2006/05/26 Two chickens in every pot, and an ASCII in every code page

2006/03/17 On the fuzzier definition of a 'Unicode application' on Win9x....

2005/12/28 Getting rid of your extra yen

2005/10/12 I'd rather call it the path separator

go to newer or older post, or back to index or month or day