Writing SQL Queries: Formatting Numbers as Currency

Sep 1

Written by:
9/1/2019 1:58 PM  RssIcon

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.

image

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')

 

image

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

 

image

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


Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
Writing SQL Queries: Formatting Numbers as Currency
Writing SQL Queries: Display Line Numbers
Welcome To My Blog