# money, commas and decimals

• ### Question

• I have decimals in the db that I add commas to by this:

CONVERT(VARCHAR, CONVERT(MONEY, SUM(GBPRevenue)), 1)

I would like to remove the decimal by rounding, what is the best way to do this?

Saturday, March 3, 2012 10:10 AM

• You can use the ROUND function:

```DECLARE @d decimal(10,2) = 3.23; SELECT @d, ROUND(@d,0), CONVERT(int,ROUND(@d,0));
--3.23	3.00	3
DECLARE @d decimal(10,2) = 3.83; SELECT @d, ROUND(@d,0), CONVERT(int,ROUND(@d,0));
-- 3.83	4.00	4```

Related article:

http://www.sqlusa.com/bestpractices2005/moneyformat/

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Saturday, March 3, 2012 10:16 AM

### All replies

• You can use the ROUND function:

```DECLARE @d decimal(10,2) = 3.23; SELECT @d, ROUND(@d,0), CONVERT(int,ROUND(@d,0));
--3.23	3.00	3
DECLARE @d decimal(10,2) = 3.83; SELECT @d, ROUND(@d,0), CONVERT(int,ROUND(@d,0));
-- 3.83	4.00	4```

Related article:

http://www.sqlusa.com/bestpractices2005/moneyformat/

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Saturday, March 3, 2012 10:16 AM
• ok, thx

if I have CONVERT(VARCHAR, CONVERT(MONEY, SUM(GBPRevenue)), 1) gives me 34,234.32

how do I change  CONVERT(VARCHAR, CONVERT(MONEY, SUM(GBPRevenue)), 1) to get 34,234?

Saturday, March 3, 2012 10:25 AM
• You can just search for the decimal point:

```DECLARE @s varchar(32) = '34,234.32'
SELECT @s, LEFT(@s,charindex('.',@s) - 1)
-- 34,234.32	34,234```

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Saturday, March 3, 2012 11:09 AM
• thx, I see, now I've got this: but it works

yikes

SELECT

SITE AS [TD align=center] ,
distinctOrders AS [TD align=right] ,
LEFT(CONVERT(VARCHAR, CONVERT(MONEY, TotalGBPRevenue), 1),
CHARINDEX('.',
CONVERT(VARCHAR, CONVERT(MONEY, TotalGBPRevenue), 1))
- 1) AS [TD align=right] ,

Saturday, March 3, 2012 11:12 AM
• Perhaps should you have a look at xsl transformation to avoid doing html
formatting in SQL.

Fred
Saturday, March 3, 2012 3:30 PM
• Interesting , however this seems tough I am sending an email form ms sql which has 4 html tables in it, all done via t-sql
Saturday, March 3, 2012 3:54 PM
• Ok, if is all done in sql, the only alternative I know is a clr
procedure but I am not sure it is better !
Le 03/03/2012 16:54, Quantum Information a écrit :
> Interesting , however this seems tough I am sending an email form ms
> sql which has 4 html tables in it, all done via t-sql

Fred
Saturday, March 3, 2012 4:21 PM