A reader sent me a question recently in which he wanted toknow how to display currency amounts in the format we commonly refer to ascurrency. (I realize how geo-centric this is, so I will attempt to beworld-centric in my response to the reader’s question.)
Let’s use the Northwind databaseas 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 abbreviatedlist). This delivers the correct answers yet not in the desiredformat. We can alter the look of the money columns, but there is a cost ofsorts. For instance, the statement inListing 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 revisedstatement in Listing B, this is notdifficult 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 showshow this results in the desired display. I should add one additional proviso that isn’t apparent.Style 1 works only on the data types moneyand smallmoney.Therefore, if you ever store your data in another type, then you can do adouble-convert, like this:
CONVERT(varchar, CONVERT(money, YourFieldName), 1)