Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
How do I format money/decimal data with commas?
From Wiki
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
- 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 first.
- DECLARE @v2 DECIMAL (36,10)
- SELECT @v2 = 13243543.56565656
- SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
- --Formatted with commas
Contributed by: --SQLDenis 02:56, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.