|How to display values as dollars and cents? Let's use the Northwind database |
as our testbed. Consider the following SQL request:
This returns a set that begins like this:
The answers are correct, but for many currencies, four decimal decimal places is too many. CONVERT() will handle that:
How do we prepend the currency sign to the amount, e.g., $1,320.00?
I should add a proviso that isn't apparent. Style 1 works only on the data types money and smallmoney. If you store monetary data as another type, you can do a double-convert, like this:
Why display money values in currency format? If the output target is Query Analyzer, 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.