Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error converting data type varchar to numeric.

Posted on 2009-05-10
18
Medium Priority
?
1,989 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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