Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I format money/decimal with commas?

T-SQL Hints and Tips

How do I format money/decimal with commas?

by  SQLDenis  Posted    (Edited  )
Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that

Below is an example

Code: 
DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  --1322323.67 	
Rounded but no formatting

SELECT CONVERT(VARCHAR,@v,1)	--1,322,323.67 	
Formatted with commas

SELECT CONVERT(VARCHAR,@v,2)	--1322323.6666  
No formatting

If you have a decimal field it doesn't work with the convert function
The work around is to convert it to money

Code: 
DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57 
Formatted with commas
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top