Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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