Data Management

Displaying money values in currency format in SQL

Arthur Fuller responds to a reader who sent him a question recently asking how to display currency amounts in currency format. Check out the solution he offers for the reader's problem.

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
    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
    '$' + 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.



Editor's Picks