Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

How do I format money/decimal data with commas?

From Wiki

Jump to: navigation, search

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

  1. DECLARE @v MONEY
  2.     SELECT @v = 1322323.6666
  3.  
  4.     SELECT CONVERT(VARCHAR,@v,0)  --1322323.67    
  5.     --Rounded but no formatting
  6.  
  7.     SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67    
  8.     --Formatted with commas
  9.  
  10.     SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666
  11.     --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 first.


  1. DECLARE @v2 DECIMAL (36,10)
  2.     SELECT @v2 = 13243543.56565656
  3.  
  4.     SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
  5.     --Formatted with commas


Contributed by: --SQLDenis 02:56, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

178 Rating: 1.0/5 (2 votes cast)