Avatar of Mark
Mark
 asked on

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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Mark

8/22/2022 - Mon
PortletPaul

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

ASKER
Paul Maxwell
why bother with the conversions in the where clause?
Because the 'newValue' column is varchar.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mark

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

mmmm, check you compatibility level perhaps?

https://msdn.microsoft.com/en-au/library/bb933794%28v=sql.120%29.aspx
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?
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

Mark

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

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.
Mark

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