question

billj avatar image
billj asked

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.
sql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

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

JohnM avatar image
JohnM answered
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!
7 comments
10 |1200 characters needed characters left characters exceeded

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 ·
JohnM, 1) Your formula add .0000, I do not want decimal number 2) Your formula does not add comma Thanks.
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 ·
Show more comments
Tom Staab avatar image
Tom Staab answered
I know John's answer was already accepted, and I realize the original question was for SQL Server 2008 R2, but I decided it might help others to know there is an easier (albeit not necessarily faster executing) way to do this in 2012 using the new format function. SELECT FORMAT(210986.8389, '###,###,###'); See the [MSDN article][1] for more information. [1]: https://msdn.microsoft.com/en-us/library/hh213505.aspx
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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