How Do I strip the decimal out of a price

Question

Post reply

How Do I strip the decimal out of a price

tschuler-738392

Ten Centuries

Points: 1172
More actions
March 5, 2009 at 11:41 am

#207231

I have an export query below that our companies software uses to export data to a flat file. I need to be able to strip the Decimal from the total_price field so that lets say 21.80 shows as 2180. The part of the query that needs changing is in bold below. Any help would be appreciated.
select
convert(varchar,'72') +
'PH'+
space(4) +
left(client_num + '.' + matter_num + ' ', 11) +
CONVERT(varchar,dbo.date_format(end_date, 'MMDDYY')) +
space(1) +
'E'+ space(1) +
case
when job_type_id = 1 then '19'
when job_type_id = 2 then '18'
when job_type_id = 5 then ' 1'
when job_type_id = 4 then '19'
end
+
space(1) +
left(convert(varchar(38),description) + ' ', 38) +
left(employee_id + ' ', 5) +
left(CONVERT(VARCHAR,CONVERT(MONEY,total_price))+' ', 6)from dbo.master_trans
where end_date > '2009-03-01 00:00:00'
--end_date < DBO.DATE_FORMAT(getdate (), 'YYYY-MM-DD 00:00:00')
and (client_num + '.' + matter_num) <> '000000.300'
--and date_export is null
--and export_id is null

Viewing 7 posts - 1 through 6 (of 6 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

I'm not sure but just:

SELECT CONVERT(INT, 21.80 * 100)

???

Greets

Flo

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 2

Thank you. I changed the line to convert only to an int and *100 like below.

left(CONVERT(int,total_price*100)+' ', 6)

the 21.80 converted to 2180. Thanks for your help.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Hey Flo! I have to ask... how did you get that picture of me first thing in the morning? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Jeff Moden (3/5/2009)
Hey Flo! I have to ask... how did you get that picture of me first thing in the morning? 😛

Picture of you?? Maybe we are twins!!

I'll gonna ask my mom...! 😀

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

florian.reischl (3/5/2009)
Jeff Moden (3/5/2009)
Hey Flo! I have to ask... how did you get that picture of me first thing in the morning? 😛
Picture of you?? Maybe we are twins!!
I'll gonna ask my mom...! 😀

Heh... I'm gonna ask my Mom, too! If I see you there, we'll know, huh? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 6

tschuler (3/5/2009)
Thank you. I changed the line to convert only to an int and *100 like below.
left(CONVERT(int,total_price*100)+' ', 6)
the 21.80 converted to 2180. Thanks for your help.

How about this REPLACE(28.20,'.','')

Kindest Regards,

Amit Lohia