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
