locked
money, commas and decimals RRS feed

  • 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

Answers

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