# question

## Remove decimal with rounding and then add comma

Hello, I would like to format the following numbers. 210986.8389 to 210,987 210986.2341 to 210,986 Any help would be appreciated.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
You can use the ROUND() function to do this: SELECT ROUND(210986.8349,0) SELECT ROUND(210986.2341,0) The output should be 210987 and 210986 respectively. Reference: https://msdn.microsoft.com/en-us/library/ms175003.aspx Hope that helps!

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shortened up a bit since it will always end in '.00' after rounding and conversion (+1): select replace(convert(varchar,convert(money,round(@number,0)),1),'.00','')
2 Likes 2 ·
Hello JohnM, The only problem is, I need to remove decimal ".00" then rounding it and add "comma". Thanks.
0 Likes 0 ·
0 Likes 0 ·
You can get rid of the trailing zeroes by casting to an INT. SELECT CAST(ROUND(210986.8349,0) AS INT)
SELECT CAST(ROUND(210986.2341,0) AS INT) As for the formatting of the comma, what is the end purpose? Ideally, whatever application is consuming the data would format the numbers accordingly. That should be done in the presentation layer not in the data layer.
0 Likes 0 ·
As a solution, you could do something like this: DECLARE @number DECIMAL(19,4) = 210986.2341
SELECT LEFT(CONVERT(VARCHAR,CAST(CAST(ROUND(@number,0) AS INT) AS MONEY),1),CHARINDEX('.',CONVERT(VARCHAR,CAST(CAST(ROUND(@number,0) AS INT) AS MONEY),1))-1) It's messy but it should return what you are looking for. Hope that helps.
0 Likes 0 · 