SQL 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
Filed under: SQL Formating | Tagged: CONVERT function, Microsoft SQL server, MSSQL, MSSQL server, SQL format money/decimal with commas, SQL formatting, SQL Server | 2 Comments »
SQLHELP 