What's wrong with convert function in where clause?

Mark
Mark used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 */

Author

Commented:
Paul Maxwell
why bother with the conversions in the where clause?
Because the 'newValue' column is varchar.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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 */
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
mmmm, check you compatibility level perhaps?

https://msdn.microsoft.com/en-au/library/bb933794%28v=sql.120%29.aspx
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
Alright, I'll leave the compatibility level as-is unless there is a compelling reason to change it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial