Displaying currency

from the Artful SQL Server & Access Tips List


How to display values as dollars and cents? 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 returns a set that begins like this:

12    14.0000     168.0000
10    9.8000      98.0000
5     34.8000     174.0000
9     18.6000     167.4000
40    42.4000     1696.0000
10    7.7000      77.0000
35    42.4000     1484.0000
15    16.8000     252.0000
6     16.8000     100.8000
15    15.6000     234.0000

The answers are correct, but for many currencies, four decimal decimal places is too many. CONVERT() will handle that:

USE Northwind
GO
SELECT
    Quantity,
    CONVERT(varchar(12), Unitprice, 1) AS Unitprice,
    CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]

12    14.00      168.00
10    9.80       98.00
5     34.80      174.00
9     18.60      167.40
40    42.40      1,696.00
10    7.70       77.00
35    42.40      1,484.00
15    16.80      252.00
6     16.80      100.80
15    15.60      234.00

How do we prepend the currency sign to the amount, e.g., $1,320.00?

USE Northwind
GO
SELECT
    Quantity,
    '$' + CONVERT(varchar(12), Unitprice, 1) AS Unitprice,
    '$' + CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]

12    $14.00      $168.00
10    $9.80       $98.00
5     $34.80      $174.00
9     $18.60      $167.40
40    $42.40      $1,696.00
10    $7.70       $77.00
35    $42.40      $1,484.00
15    $16.80      $252.00
6     $16.80      $100.80
15    $15.60      $234.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:

CONVERT(varchar, CONVERT(money, YourFieldName), 1)

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.

Return to the Artful SQL Server & Access Tips page