Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


ms sql server 2005 error converting varchar to numeric

Posted on 2011-09-28
Medium Priority
Last Modified: 2012-05-12
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 @home_spread not like '%.%'
     set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'
Question by:GlobaLevel
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Author Comment

ID: 36720784
Any ideas?
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 36813856
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
LVL 22

Accepted Solution

8080_Diver earned 2000 total points
ID: 36814284
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

LVL 22

Expert Comment

ID: 36814289
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

LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36814649

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
     set @home_spread_numeric = cast(@home_spread as numeric(30,1))

Open in new window


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question