[mssql] How do I format money/decimal with commas?

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

DECLARE @v2 DECIMAL (36,10)

SELECT @v2 = 13243543.56565656

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57

/* Formatted with commas */