SQL Query strange error

Question

When I execute this query the following error is displayed.

[Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.]

I am wondering that the error is in the comparison part that is fb_userevents.userid = '100002917025417' when this long string within the single quot is replaced by 1 or 0 it works. Instead of quote (') around it why it tries to convert into float??

I am using SQL Server 2005

select
    fb_event.eventname
from
    fb_event
inner join
    fb_userevents on fb_userevents.eventid = fb_event.eventid  
where 
    fb_userevents.userid = '100002917025417' 
    and DATEDIFF(hh,fb_event.startdate,getdate()) < 0 
    and  acos(sin(0) * sin(convert(float,altitude)) + 
         cos(0) * cos(convert(float,altitude)) *
         cos(convert(float,longitude) - 101)) * 6371 <= 1000

The altitutes and longitutes of that user: enter image description here

oh of course. I forgot id fields should be strings. And there's me using integers.... — Mitch Wheat
– Mitch Wheat, Commented Oct 31, 2011 at 12:45
The error will be coming from an invalid value in altitude or longitude — Martin Smith
– Martin Smith, Commented Oct 31, 2011 at 12:46
what happens if you try '25417' for the value? As Martin is pointing out that's a really big number that may be getting treated differently. Martin may be able to provide that as the answer if so. — Michael Durrant
– Michael Durrant, Commented Oct 31, 2011 at 12:47
@MichaelDurrant - I deleted that part of my comment because the OP says userid is varchar and they are passing a string to it so there should be no numeric casting going on there at all in that bit. Clearly the values being returned when the where clause is changed are just different and not valid numbers. — Martin Smith
– Martin Smith, Commented Oct 31, 2011 at 12:49

bv8z · Accepted Answer · 2011-10-31 12:47:47Z

4

Are you sure the problem isn't with this line?

cos(convert(float,altitude))

What data type is altitude? And does every value in that column only contain numeric characters?

answered Oct 31, 2011 at 12:47

bv8z

9753 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin Smith Over a year ago

@XXXcoder - That's what you think! Of course you can't guarantee this as you are using the wrong datatype. Try select convert(float,altitude),convert(float,longitude) from your_table

MatBailie Over a year ago

@XXXcoder - I'm strongly inclined to agree with Martin that, if altitude or longitude are VARCHAR fields, you have at least one string value that can not be converted to a FLOAT value.

Shahid Karimi Over a year ago

@Dems, I have added the screen shot of the filtered alt and long of that user. Can you see any unconvertable thing thre?

Martin Smith Over a year ago

@XXXcoder - It doesn't have to belong to that user. The CONVERT can get pushed to before the WHERE so potentially any invalid value in those columns can mess you up.

MatBailie Over a year ago

@XXXcoder - Factors such as indexes can cause (as Martin states) the Trigonometric condition to be evaluated before the user_id condition. To check this, you could look at the execution plan of the query. Additionally, I would recommend sanity checking all records in your table to see if the values can be converted to FLOAT or not.

keithwarren7 · Accepted Answer · 2011-10-31 12:44:08Z

0

You may need to do an explicit cast or convert on that field.

answered Oct 31, 2011 at 12:44

keithwarren7

14.3k8 gold badges57 silver badges75 bronze badges

Collectives™ on Stack Overflow

SQL Query strange error

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related