Error converting data type varchar to numeric.

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

rowmarkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
rowmarkAuthor Commented:
I imported data using Import/Export wizard which created the table.
Now I am not able to alter the column datatype.


0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show sample data?
0
 
rowmarkAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, seems fine...
now, what is the output of
select cast( cast( 1 / 2 as float) as varchar(10))

Open in new window

0
 
RiteshShahCommented:
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
 
SharathData EngineerCommented:
sometimes the askers accepts crazy posts as accepted solution.
0
 
Anthony PerkinsCommented:
>>sometimes the askers accepts crazy posts as accepted solution.<<
Careful.
0
 
SharathData EngineerCommented:
acperkins - do you really agree with the accepted post as the answer/solution for this question?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
SharathData EngineerCommented:

>> 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1 / 2 always returns 0 ( this is integer / integer )
good catch !!!
I should have written 1.0 / 2.0  :/

0
 
SharathData EngineerCommented:

>> 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
 
Anthony PerkinsCommented:
>>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
 
SharathData EngineerCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.