asked on

ms sql server 2005 error converting varchar to numeric

not sure why are having issues converting here..

declare @home_spread nvarchar(max)

declare @home_spread_numeric numeric(30,2)

-- the value will look like '+33.5' or '-4.5 or +7'
set @home_spread = (select home_team_spread from schedule_shoe where shoe_id = @shoe_id)

-- ==================================================
set @home_spread = (replace(@home_spread,'-',''))

set @home_spread = (replace(@home_spread,'+',''))

-- =================================
-- IF NO DECIMAL EXISTS PROVIDE IT...
-- ======================================
if @home_spread not like '%.%'
begin
set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'
end

GlobaLevel

ASKER

Any ideas?

Vitor Montalvão

Where it fails?
And in the last line

set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'

where is the @away_spread came from? You don't need the " +'0' " because you are converting already to 2 decimal places

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

8080_Diver

Alternatively you could use:

SET @home_spread = @home_spread + '.0'
set @home_spread_numeric = cast(@home_spread as numeric(30,2))

Open in new window

Bhavesh Shah

Hi,

I made bit change in SQL.

check out.

declare @home_spread nvarchar(max),@shoe_id int


declare @home_spread_numeric numeric(30,2)

-- the value will look like '+33.5' or '-4.5 or +7'
set @home_spread = (select Replace(Replace(home_team_spread,'+',''),'-','') from schedule_shoe where shoe_id = @shoe_id)

IF IsNumeric(@home_spread)=1
begin
     set @home_spread_numeric = cast(@home_spread as numeric(30,1))
end

Open in new window