Remove decimal with rounding and then add comma

Question

question

billj asked Oct 23, '15

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

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

Answer 1 · 2015-10-23T18:17:05Z

JohnM answered Oct 23, '15

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

10 |1200

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

KenJ commented · Oct 23, 2015 at 08:27 PM

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 ·

billj commented · Oct 23, 2015 at 06:24 PM

Hello JohnM, The only problem is, I need to remove decimal ".00" then rounding it and add "comma". Thanks.

0 ·

billj commented · Oct 23, 2015 at 06:37 PM

JohnM, 1) Your formula add .0000, I do not want decimal number 2) Your formula does not add comma Thanks.

0 ·

JohnM commented · Oct 23, 2015 at 06:40 PM

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 ·

JohnM commented · Oct 23, 2015 at 06:47 PM

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 ·

Show more comments

Answer 2 · 2015-10-23T21:34:32Z

Tom Staab answered Oct 23, '15

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

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

question