Answered by:
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
Answers
-
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
- Marked as answer by Quantum Information Saturday, March 3, 2012 10:16 AM
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
- Marked as answer by Quantum Information Saturday, March 3, 2012 10:16 AM
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 htmlformatting in SQL.
FredSaturday, 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-sqlSaturday, March 3, 2012 3:54 PM
-
Ok, if is all done in sql, the only alternative I know is a clrprocedure 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
FredSaturday, March 3, 2012 4:21 PM