Problem converting data

[Jenns]

Hi,

I've got a varchar field that has decimal data in it. For example: 20.00, 120.00, 1120.00. I need to get the data to look like 20, 120, 1120 to link it up to ID numbers in another table. I tried converting the data to float, int and numberic, but each time it creates an error. Does anyone have any suggestions?

Thanks!
Jenn

 

[Jamfool]

declare @a as varchar(250)
select @a = '1120.00'

select cast(cast (@a as float) as int)

--however with the '.' on the end u would need to 
select @a = '1120.00.'

select  
case when right(@a,1) = '.' then 
		cast(cast (left(@a ,len(@a)-1) as float) as int)
     else
		cast(cast (@a as float) as int)
end

 

[Jenns]

Thanks for the help. Your code works just fine, but when I subsitute my field name for your variable I still get "Error converting data type varchar to float".


select cast(cast (myField as float) as int) as myField
from myTable.

Why could it convert one way and not the other?

Thanks!
Jenn

 

[Jenns]

Ah huh! I found out why. There are commas in there too. Rats! Now how do I handle that?

Thanks!
Jenn

 

[gmmastros]

Since you are starting with a varchar field, you can replace the commas with nothing.

select cast(cast (Replace(myField, ',', '') as float) as int) as myField
from myTable.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jenns]

That works perfectly! Thanks so much!

Thanks!
Jenn