July 21, 2009 at 9:17 am
I have a process that recently had a value of '8.5 ' being compared to the integer 5. In 2000, this comparison works just fine, as the varchar value of '8.5 ' is implicitly converted to 8.5 and then the comparison is done.
However, in SQL 2008, I recieve this error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '8.5 ' to data type int.
I have already resolved this issue manually by converting to an Explicit comparitive and by wrapping the field in an LTRIM & RTRIM statement to remove the trailing spaces.
Should SQL 2008 be able to handle this implicitly, however? Is there a compilor directive that will correct this issue?
July 21, 2009 at 4:37 pm
Mark,
I have to chuckle to myself as I just went through this with a co-worker the other day who was ranting about SQL not being able to figure what he wanted.
If I give you a select statement like:
SELECT '8.5' + 5
Can you guess what I want for output? Maybe '8.55' or '13.5' or 13.5 or 13 or 14?
You might guess correctly, or you might not. That is why explicit conversions are so important. That asside, I do not know of any setting that will do what you want, unless setting the compatability level back a version or two would work.?.?
July 21, 2009 at 8:27 pm
There was more to the code, but suffice it to say it doesn't work in 2000 either. So, we'll just have to deal with it. As I noted earlier, I've already worked it out by Wrapping it in LTRIM(RTRIM()) and casting it.
July 21, 2009 at 8:28 pm
I just noticed that I said this worked in 2000, but it doesn't. So, disregard.
July 22, 2009 at 9:21 am
Forgot to say, Thanks for the kick in the head. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply