Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error converting data type varchar to numeric.

Posted on 2009-05-10
18
Medium Priority
?
2,000 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

971 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