A reader sent me a question recently in which he wanted to
know how to display currency amounts in the format we commonly refer to as
currency. (I realize how geo-centric this is, so I will attempt to be
world-centric in my response to the reader’s question.)
Let’s use the Northwind database
as our testbed. Consider the following SQL request:
USE Northwind
GO
SELECT
Quantity,
Unitprice,
Quantity * UnitPrice AS Amount
FROM [Order Details]
This request results in a set that looks like
Listing A (which is an abbreviated
list). This delivers the correct answers yet not in the desired
format. We can alter the look of the money columns, but there is a cost of
sorts. For instance, the statement in
Listing B delivers what we want (i.e., amounts formatted as currency).
The reader also wanted to know how to get the currency sign prepended to the amount, e.g., $1,320.00. Given the revised
statement in Listing B, this is not
difficult to do, as you can see here:
USE Northwind
GO
SELECT
Quantity,
'$' + CONVERT(varchar(12), Unitprice, 1) AS Unitprice,
'$' + CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]
Listing C shows
how this results in the desired display. I should add one additional proviso that isn’t apparent.
Style 1 works only on the data types money
and smallmoney.
Therefore, if you ever store your data in another type, then you can do a
double-convert, like this:
CONVERT(varchar, CONVERT(money, YourFieldName), 1)
Why display money values in currency format?
Now that we’ve created what the reader requested, you may be
asking yourself: Why would you want to display money values in currency format?
Assuming that the output target is Query Analyzer, then it makes sense to want your format to look more
attractive. But how many users in your organization actually use Query Analyzer
to do their jobs? My guess is fewer than five. Most users opt for applications
written in Microsoft Access, .NET, Excel, or Delphi, whose formatting functions
surpass T-SQL’s by a long shot. T-SQL is not designed for elaborate formatting,
but rather efficient delivery of the underlying data.
However, you may have a legitimate reason to deliver the
data in this format; if so, there is a cost of sorts. The column as delivered
is no longer money or even numeric, so you can’t perform arithmetic functions
(i.e., average, sum, min, max, etc.) within it. If you require such operations,
you will have to add the actual column to the SELECT statement, and then
perform the operations on that column.
Why offer server code instead of client code?
T-SQL provides solutions to this display problem as
illustrated above, but are these the right solutions for your situation? The
bottom line here is separation of the front from the back end.
Most of the time, your solution to this problem lies in
client code rather than SQL code. However, I recognize that there are some
situations in which it is best handled in server code. For example, Access, VB,
and .NET offer the Format() function, which handles
this problem nicely. This won’t always work; for example, you might be feeding
a text file into some program that is expecting money amounts to be displayed
in exactly one way.
So, if you do need the server code, now you have it. But
before you implement it, realize the cost as described above.
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!