June 23, 2016 at 11:17 am
Hello,
I have script:
Select CASE WHEN (ABS.VALUE<90 AND ABC1.VALUE<140) THEN 1 ELSE 0 END AS status
returns "Conversion failed when converting the varchar value '118.' to data type int." error when ABS.Value or ABS1.Value aren't numbers.
using isnumeric, did't help - returns 1 for all numeric values
Can anyone please help.
Thanks.
Al.
June 23, 2016 at 11:39 am
al.yukhvidin (6/23/2016)
Hello,I have script:
Select CASE WHEN (ABS.VALUE<90 AND ABC1.VALUE<140) THEN 1 ELSE 0 END AS status
returns "Conversion failed when converting the varchar value '118.' to data type int." error when ABS.Value or ABS1.Value aren't numbers.
using isnumeric, did't help - returns 1 for all numeric values
Can anyone please help.
Thanks.
Al.
Read this article: http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/
June 23, 2016 at 12:08 pm
Thank you. Create article.
Didn't help much to solve my problem.
June 23, 2016 at 1:10 pm
If you're on SQL 2012 or later (as per the forum the question is in), you can add Try_Cast or Try_Convert to the code.
Select CASE WHEN (TRY_CAST(ABS.VALUE AS INT)<90 AND TRY_CAST(ABC1.VALUE AS INT)<140) THEN 1 ELSE 0 END AS status
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 23, 2016 at 1:14 pm
SSChampion, Thank you.
Works perfect!
Al.
June 23, 2016 at 1:21 pm
The performance on that might not be great, but it should work.
Better would be to have a typed column with Int data in it, but if you can't get that, this might be all that's available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy