Solved

Error converting data type varchar to numeric.

Posted on 2009-05-10
18
1,979 Views
Last Modified: 2012-05-06
Hello,

I have zipcodes table in which both Longitude and Latitude columns are varchar datatype.

In my stored procedure I am converting them into float even then I am getting an error saying Error converting data type varchar to numeric.

Please help.
/****** Object:  StoredProcedure [dbo].[up_FindZipCodesWithinRadius]    Script Date: 05/10/2009 14:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[up_FindZipCodesWithinRadius]
 
	@ZipCode char(5) ,
	@GivenMileRadius int
AS
SET NOCOUNT ON
 
DECLARE @lat1 float, 
	@long1 float
 
SELECT  @lat1= CAST(Latitude as float),
        @long1 = CAST(longitude as float)
FROM ZipCodes
WHERE zipcode = @ZipCode
 
SELECT ZipCode ,DistanceInMiles
FROM
(
	SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + 
				(Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) / 
				((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * 
				Cos((longitude/57.2958) - (@Long1/57.2958)))))) As DistanceInMiles
	FROM ZipCodes
) a
WHERE a.DistanceInMiles <= @GivenMileRadius
--AND ZipCode <> @ZipCode
ORDER BY DistanceInMiles

Open in new window

0
Comment
Question by:rowmark
[X]
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
  • 6
  • 5
  • 2
  • +2
18 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24349543
>I have zipcodes table in which both Longitude and Latitude columns are varchar datatype.
why not float, then?

anyhow, how is the data stored?
0
 

Author Comment

by:rowmark
ID: 24349555
I imported data using Import/Export wizard which created the table.
Now I am not able to alter the column datatype.


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24349566
can you show sample data?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:rowmark
ID: 24349577
Zipcode Latitude      Longitude
00501      40.81      -73.04
00544      40.81      -73.04
00601      18.16      -66.72
00602      18.39      -67.18
00603      18.45      -67.14
00604      18.49      -67.13
00605      18.46      -67.14
00606      18.17      -66.94
00610      18.28      -67.13
00611      18.27      -66.8
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24349585
ok, seems fine...
now, what is the output of
select cast( cast( 1 / 2 as float) as varchar(10))

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 24351292
i didn't get any error for the sample set provided. seems like you have non-numerical data in your Latitude or Longitude columns.
first run this query and check whether you have non-numerical data in these columns.
select *
from ZipCodes
where ISNUMERIC(Longitude) <> 1 OR ISNUMERIC(Latitude) <> 1

declare @ZipCodes table(ZipCode nvarchar(10),Latitude nvarchar(10),Longitude nvarchar(10))
insert into @ZipCodes values
('00501',      '40.81',      '-73.04'),
('00544' ,     '40.81' ,     '-73.04'),
('00601'  ,    '18.16'  ,    '-66.72'),
('00602'   ,   '18.39'   ,   '-67.18'),
('00603'    ,  '18.45'    ,  '-67.14'),
('00604'     , '18.49'     , '-67.13'),
('00605'      ,'18.46'      ,'-67.14'),
('00606'      ,'18.17'      ,'-66.94'),
('00610'      ,'18.28'      ,'-67.13'),
('00611'      ,'18.27'      ,'-66.8')
 
declare @ZipCode char(5) ,@GivenMileRadius int
set @ZipCode = '00501'
 
 
DECLARE @lat1 float, 
	@long1 float
 
SELECT  @lat1= CAST(Latitude as float),
        @long1 = CAST(longitude as float)
FROM @ZipCodes
WHERE zipcode = @ZipCode
 
SELECT ZipCode ,DistanceInMiles
FROM
(
	SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + 
				(Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) / 
				((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * 
				Cos((longitude/57.2958) - (@Long1/57.2958)))))) As DistanceInMiles
	FROM @ZipCodes
) a
--WHERE a.DistanceInMiles <= @GivenMileRadius
--where ZipCode <> @ZipCode
ORDER BY DistanceInMiles
 
ZipCode	DistanceInMiles
00501	0
00544	0
00604	1581.45534930274
00605	1583.355105468
00603	1584.03211068932
00602	1587.57353320362
00610	1595.67317177761
00611	1600.7641995769
00606	1605.62755014986
00601	1609.29724853688

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24351311
I guess there is no problem in conversion but there is problem in your calculation, you may be getting 0. I guess, you have data in all the rows as you saw above, no column in null or blank.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24372153
sometimes the askers accepts crazy posts as accepted solution.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24374305
>>sometimes the askers accepts crazy posts as accepted solution.<<
Careful.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24378133
acperkins - do you really agree with the accepted post as the answer/solution for this question?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24378197
I think I will have to explain, for future readers, what I tried to point out with my suggestion:

select cast( cast( 1 / 2 as float) as varchar(10))

can return either
0.5
or
0,5

depending on some settings for the server vs the logins' language.
the varchar fields do contain a "." for the decimal symbol, which will fail on
cast('0.5' as float)  
if the session uses "," for the decimal symbol, with exactly the error message posted.


0
 
LVL 41

Expert Comment

by:Sharath
ID: 24379731

>> select cast( cast( 1 / 2 as float) as varchar(10))
 
1 / 2 always returns 0 ( this is integer / integer ) and if you cast it to float and then varchar, you will always get a 0.
To my knowledge, there is no chance of getting 0.5 or 0,5 with your statement.
>> depending on some settings for the server vs the logins' language.
can you provide the server setting in which your statement results in 0.5 or 0,5 ?
I am curious to know the server settings in which your statement will give 0.5 or 0,5.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24379755
>1 / 2 always returns 0 ( this is integer / integer )
good catch !!!
I should have written 1.0 / 2.0  :/

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24380405

>> I should have written 1.0 / 2.0 :/
Thats the reason for my previous posts. I dont know whether the asker tried your suggestion or not but he accepted your post as answer and there are no more comments from the asker also.
Is it possible for you to provide the server settings that will display 0,5 for your modifed statement.
select cast( cast( 1.0 / 2.0 as float) as varchar(10))
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24380573
>>do you really agree with the accepted post as the answer/solution for this question? <<
It does not matter what I agree with or not, it is the tone used, especially to someone like angelIII.  To quote the EE Guidelines:

<quote>
Be professional: Treat the asker and your colleagues as professionals. Check your ego and your attitude at the door; rudeness, derogatory comments, and sarcastic remarks are uncalled for and unnecessary.

Avoid criticizing: There's nothing to be gained by criticizing another Member when disagreeing with his/her suggestions. Don't take a critical comment personally; stay focused on the object -- solving the asker's problem.
</quote>

And yes, I have been equally guilty in the past and somebody has probably pointed it out to me.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24381031

My first post (24372153) is not for angelIII. angelIII has asked a question in his post (24349585) and was trying to help the asker. Solution is not found at that moment. Later myself and Ritesh provided some more suggestions but suddenly the asker accepted a post as solution and there were no more comments from him also. Whether commenting further or not is upto the asker's wish. I raised concern as there was a bug in angelIII post which he accepted later. i don't understand what made the asker to accept that post.
EE is one of the famous sites in which many people search for solutions everyday. If someone come across this question and looked at the answer, obviously they come to a conclusion that the accepted solution is not the correct solution for the problem.
I am a professional and behave like a professional. I don't take anything personal here and expect the same from everyone. Anyway thanks for your EE guidelines again. Apologies me if my comments hurt anyone. My intention is not to hurt anyone but for solutions to the problems.
I respect all the experts here for their valuable comments irrespective of he/she is a savant/Master or not certified.
My last post in this thread. Lets move on to next question.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

726 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