[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL format 10000 as 10,000

Posted on 2014-08-26
6
Medium Priority
?
313 Views
Last Modified: 2014-08-26
In the following code I am able to add comas using CAST(... money) but it comes with trailing .00

Question: How can I get rid of these trailing .00?

I have tried:

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')

but, maybe this is dynamic sql, the highlighted portion above produces error. (Incorrect syntax near '.00').

Thank you.
	SELECT @nsql = 'SELECT t.LastYearCount  as [' + @LY + '], 
	CONVERT(varchar(20), (CAST(t.CurrYearCount AS money)), 1)  as [' + @YYYY + '], 
	convert(varchar,convert(decimal(8,1),(t.IncreaseOverLastYear/100))) as [%]  
	FROM t14StatType AS s INNER JOIN (t08MedCenter AS m INNER JOIN t10YearlyCount AS t 
	ON m.MedCtrID = t.MedCtr_ID) ON s.StatTypeID = t.StatType_ID 
	WHERE (((s.StatTypeID)=' + CONVERT(varchar(100),@StatType_ID) + ') AND ((t.YYYY)=' + @YYYY + ') AND ((m.ExcludeYN)=0) 
	   AND ((t.Region_ID)=' + CONVERT(varchar(100),@RegionID) + ')) ORDER BY m.Sort;' 

    EXEC (@nsql)

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 3
  • 3
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40287060
which version of SQL Server is this for please? (or, which versions)
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40287068
2008 r2
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40287076
mmm, see is STR() or FLOOR() help perhaps?

declare @m as money = 10000.12

select @m, str(@m,8,0), floor(@m)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40287078
mmm(2), see is STR() or FLOOR()or ROUND  help perhaps?

declare @m as money = 10000.12

select @m, str(@m,8,0), floor(@m), round(@m,0)
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40287101
replace(convert(varchar(20),CAST(t.LastYearCount  AS money), 1),''.00'','''')

I had to double single quotes.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40287103
Thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question