Solved

Error converting data type varchar to numeric.

Posted on 2009-05-10
18
1,973 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
  • 6
  • 5
  • 2
  • +2
18 Comments
 
LVL 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24349566
can you show sample data?
0
 

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 142

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 40

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 40

Expert Comment

by:Sharath
ID: 24372153
sometimes the askers accepts crazy posts as accepted solution.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

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

Expert Comment

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

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 40

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 142

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 40

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 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now