[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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
0
GlobaLevel
Asked:
GlobaLevel
1 Solution
 
GlobaLevelAuthor Commented:
Any ideas?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
 
8080_DiverCommented:
I suspect thaqt the line in question shoud read:
set @home_spread_numeric = cast(@home_spread as numeric(30,2)) + '.0'

Open in new window


As previously stated, forget the '.0', not only because it is unneeded but also because you are trying to add an Alphanumeric (i.e. CHAR or VARCHAR) to a numeric.  If you feel compelled to add 0 tenths to the value, use:
set @home_spread_numeric = cast(@home_spread as numeric(30,2)) + 0.0

Open in new window

0
 
8080_DiverCommented:
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

0
 
Bhavesh ShahLead AnalysistCommented:
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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now