Writing SQL Queries: Formatting Numbers as Currency

Data results that represent money or currency display as unformatted. In this post I will show how to format the results as currency.

TOOLS: Microsoft SQL Server Management Studio v17.3; AdventureWorks2008

TASK: Display unformatted SQL results as currency

SOLUTION:

The image below shows the unformatted SQL results.

To display the ‘Sales Last Year’ and ‘Sales YTD’ results as currency add the highlighted function to your query.

FORMAT(SalesLastYear, 'C')

FORMAT(SalesYTD , 'C')

To display the results as different currencies add culture code to the currency format function. A few examples are listed below.

FORMAT(SalesLastYear, 'C', 'en-US') USA

FORMAT(SalesLastYear, 'C', 'pt-br') Brazil

FORMAT(SalesLastYear, 'C', 'en-gb') Great Britain

FORMAT(SalesLastYear, 'C', 'de-de') German

FORMAT(SalesLastYear, 'C', 'zh-cn') Chinese

FORMAT(SalesLastYear, 'C', 'ja-jp') Japanese

FORMAT(SalesLastYear, 'C', 'it-it') Italian

Reference link to comprehensive culture code list (SQL-Server-Helper.com) - http://www.sql-server-helper.com/sql-server-2012/format-string-function-culture.aspx