Databases
StackOverflow2013
Postsdbo
POError converting data type varchar to numeric CAST not working
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POError converting data type varchar to numeric CAST not working
    primarykey
    data
    text
    <p>I know this has been beaten like a dead horse. However no matter how I slice it, cast it or convert it I have the same issue. Error converting data type varchar to numeric.</p> <pre><code>SELECT property_id, property_case_number, property_address, property_city, property_state, property_zip, property_lon, property_lat FROM property WHERE (property_active = 1) AND (property_county = (SELECT property_county FROM property AS property_1 WHERE (property_id = 9165))) AND (property_id &lt;&gt; 9165) AND property_lon IS NOT Null AND property_lat IS NOT Null AND dbo.LatLonRadiusDistance( CONVERT(DECIMAL(15,12),(select property_lat from property where property_id = 9165)), CONVERT(DECIMAL(15,12),(select property_lon from property where property_id = 9165)), property_lat,property_lon) &lt;= '5' </code></pre> <p>I run into this issue as soon as I add dbo.LatLonRadiusDistance at the end.</p> <p>dbo.LatLonRadiusDistance compares lat &amp; lon distance in miles.</p> <pre><code>FUNCTION [dbo].[LatLonRadiusDistance] ( @lat1Degrees decimal(15,12), @lon1Degrees decimal(15,12), @lat2Degrees decimal(15,12), @lon2Degrees decimal(15,12) ) RETURNS decimal(9,4) AS BEGIN DECLARE @earthSphereRadiusKilometers as decimal(10,6) DECLARE @kilometerConversionToMilesFactor as decimal(7,6) SELECT @earthSphereRadiusKilometers = 6366.707019 SELECT @kilometerConversionToMilesFactor = .621371 -- convert degrees to radians DECLARE @lat1Radians decimal(15,12) DECLARE @lon1Radians decimal(15,12) DECLARE @lat2Radians decimal(15,12) DECLARE @lon2Radians decimal(15,12) SELECT @lat1Radians = (@lat1Degrees / 180) * PI() SELECT @lon1Radians = (@lon1Degrees / 180) * PI() SELECT @lat2Radians = (@lat2Degrees / 180) * PI() SELECT @lon2Radians = (@lon2Degrees / 180) * PI() -- formula for distance from [lat1,lon1] to [lat2,lon2] RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2) + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2))) * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4) END </code></pre> <p>I'm sure it's something to do with </p> <pre><code>(select property_lat from property where property_id = 9165) </code></pre> <p>But no matter how I cast or convert it doesn't change things.</p> <p>And if I run the function by itself it doesn't give an error.</p> <p>Anyone have any insights?</p> <p>here is a sample row </p> <pre><code>8462 023-125514 15886 W MOHAVE ST GOODYEAR AZ 85338-0000 -112.400297000000 33.429041000000 </code></pre> <p>property_lat &amp; property_lon are varchar(50)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPrescient
    1. USPrescient
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COdoes select property_lat from property where property_id = '9165' (quotes) work?
      singulars
      1. POError converting data type varchar to numeric CAST not working
      1. USValamas
    2. COYes it does. in management studio I get back 23 rows. Along with the error. I just verified that it does. (select property_lat from property where property_id = 9165) works
      singulars
      1. POError converting data type varchar to numeric CAST not working
      1. USPrescient
    3. COWhy are you saying `<= '5'` instead of `<= 5` (no quotes)? Also what are the data types of `property_lat` and `property_lon`? Can you show us a few sample rows that produce the error so we don't have to guess?
      singulars
      1. POError converting data type varchar to numeric CAST not working
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload