How to trim 0''s ?

Question

How to trim 0''s ?

Jeff-67583

SSCertifiable

Points: 5625
More actions
April 8, 2005 at 2:37 pm

#65847

Hi,
Any suggestions on how to trim the decimal places off:
1,234,567.00 >> 1,234,567

Many thanks. Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

CAST() or CONVERT() to an integer is my first thought. If it's a string, you can SUBSTRING using CHARINDEX to find the decimal point.

K. Brian Kelley
@kbriankelley

ron k SSCertifiable Points: 6103 More actions · Answer 2

declare @var varchar(20)

set @var = '1,234,567.00'

select Left(@var,len(@var) -3)

-------------------

1,234,567

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 3

Hi, I used th datatype "money" to display commas ... but would like to chop off the decimals ... not sure if i can use variable in xp_sendmail in @query line?

here is the code i'm using ...

SELECT CAST(CONVERT(varchar,CAST(filesize_kb AS money),1)AS varchar) AS "FILESIZE_kb" FROM xyz

Many thanks. Jeff

ron k SSCertifiable Points: 6103 More actions · Answer 4

You might want to take a look at this thread. http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=170943