What's wrong with convert function in where clause?

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

Paul Maxwell

why bother with the conversions in the where clause?

Because the 'newValue' column is varchar.

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.

mmmm, check you compatibility level perhaps?

https://msdn.microsoft.com/en-au/library/bb933794%28v=sql.120%29.aspx

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)

Select allOpen in new window

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.

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.

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