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.0000The 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.
Last updated 22 May 2024
