What's wrong with convert function in where clause?

I have the query shown below. The "convert(smallmoney,newvalue)" works fine in the select statement, but when I put it in the where clause I get the error:

"Select Failed: 293 Cannot convert char value to smallmoney. The char value has incorrect syntax."

What's am I doing wrong?
select top 10 c.memberId, m.memberName, convert(smallmoney,newvalue)
from changeLog c
join members m on m.memberId = c.memberId
join members a on a.memberType = 'ALT' and a.retireeId = m.memberId
where keyFieldExtra like 'COLA%' and year(changeTime) >= 2014
and convert(smallmoney,newValue) > convert(smallmoney,0.03)

Open in new window

I've also tried without the "convert(smallmoney,0.03)" in the where clause.
LVL 1
MarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
why bother with the conversions in the where clause?

where keyFieldExtra like 'COLA%' and year(changeTime) >= 2014
and newValue > 0.03

The general principle for functions on data in the where clause is to avoid them - for performance reasons mostly, but avoiding them can also avoid reliability issues too. When you use a function on data you remove the ability to use an index on that column.

sargable: Search ARGument ABLE
...
Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
https://en.wikipedia.org/w/index.php?title=Sargable&oldid=573806804

another example of something to avoid is and year(changeTime) >= 2014
it can be replaced by:  and changeTime >= '20140101' /* >= 1st Jan 2014, no data conversion needed */
MarkAuthor Commented:
Paul Maxwell
why bother with the conversions in the where clause?
Because the 'newValue' column is varchar.
PortletPaulEE Topic AdvisorCommented:
Sorry, yes I see that now - forgive me please.

The syntax is acceptable in your select clause because it has not yet been executed (the FROM clause and WHERE clause are both performed before the SELECT), So, the error is caused by some faulty data in the newValue column that isn't numeric most probably and cannot be converted.

If you have SQL Server 2012 or later you could use TRY_CONVERT()

if that isn't available then ISNUMERIC() may be sufficient to help you, but this isn't failproof.

e.g.

where try_convert(smallmoney,newValue) > 0.03

or

select * from (
         select * from table
         where isnumeric(newValue) = 1
        )
where convert(smallmoney,newValue) > 0.03


==============
this remains true:
avoid: and year(changeTime) >= 2014
replacement:  and changeTime >= '20140101' /* >= 1st Jan 2014, no data conversion needed */

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
MarkAuthor Commented:
Interesting, I have SQL Server 2014, but with try_convert(smallmoney,newValue) in the where clause I get the error:

Select Failed: 195 'try_convert' is not a recognized built-in function name.

The isNumeric() test seems to work. I'll play with it some more.
PortletPaulEE Topic AdvisorCommented:
mmmm, check you compatibility level perhaps?

https://msdn.microsoft.com/en-au/library/bb933794%28v=sql.120%29.aspx
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just to check if this can be a decimal/float issue.
Can you run the following two statements and confirm that no error returned?
select top 10 c.memberId, m.memberName, convert(smallmoney,newvalue)
from changeLog c
join members m on m.memberId = c.memberId
join members a on a.memberType = 'ALT' and a.retireeId = m.memberId
where keyFieldExtra like 'COLA%' and year(changeTime) >= 2014
and convert(smallmoney,newValue) > convert(smallmoney,'0.03');

select top 10 c.memberId, m.memberName, convert(smallmoney,newvalue)
from changeLog c
join members m on m.memberId = c.memberId
join members a on a.memberType = 'ALT' and a.retireeId = m.memberId
where keyFieldExtra like 'COLA%' and year(changeTime) >= 2014
and convert(smallmoney,newValue) > convert(smallmoney,0)

Open in new window

MarkAuthor Commented:
Paul Maxwell
mmmm, check you compatibility level perhaps?
Compatibility level is "SQL Server 2008 (100)". That probably explains the try_convert problems as it appears that was implemented for SQL Server 2012 and later. Not sure why my compatibility level is 2008. Would that have been an option I was asked at install time? I restored this database, but from a 2005 backup. Could I simply change the compatibility level without destroying everything?

Vitor Montalvão
Just to check if this can be a decimal/float issue.
Can you run the following two statements and confirm that no error returned?
Both queries give me:

Cannot convert char value to smallmoney. The char value has incorrect syntax.

Without examining the actual data, my guess is that the 'newValue' column in some of these selected records is null. Probably Paul Maxwell's isnumeric() check is the best I can do.
PortletPaulEE Topic AdvisorCommented:
Altering the compatability level requires testing and to be honest its too big of a question to answer simply.  Think of it as an upgrade ... its possible of course but should not be done hastily.

Looks like isnumeric is your way forward for now.
MarkAuthor Commented:
Alright, I'll leave the compatibility level as-is unless there is a compelling reason to change it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.