Typically the formatting of data is something that should be performed at the front-end however there are times when there is a need to format data on the back-end. The following example illustrates how a number stored in a table can be formatted in accounting format ie. $000,000.00.
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT ‘$’ + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
Returns:
———————
$999,999.00
$1,000.00
$10.00
(3 row(s) affected)
