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?

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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] ,

Answer 5

Perhaps should you have a look at xsl transformation to avoid doing html

formatting in SQL.

 

---

Fred

Answer 6

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

Answer 7

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