Format Decimal Datatype with Commas in T-SQL

Note: This is approach is actually NOT recommended. Better idea would be to do all the formatting on UI.

Ever fall into a situation where a column is a Decimal, and you want the output to have a comma after every three digits?
T-SQL’s CONVERT function can do it for you. The syntax of the function is:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Here, we will be utilizing the power of the last parameter style. The style can be set to the following three values, when converting money datatype into a character datatype:

0 – No commas and only 2 decimal points
1 – Commas every 3 digits, and only 2 decimal points
2 – No command, and 4 decimal points.

Now since, the datatype stored in the table is actually decimal, we first need to convert it into money, and then convert it into varchar or char.

select convert(varchar(50),convert(money,'54120000.2154'),1)

The result would be:

-------------------- 54,120,000.22 (1 row(s) affected)¬†Simple. Isn’t it?

Leave a Reply

Your email address will not be published. Required fields are marked *