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)
I've also tried without the "convert(smallmoney,0.03)"why bother with the conversions in the where clause?Because the 'newValue' column is varchar.
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)
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?
Just to check if this can be a decimal/float issue.Both queries give me:
Can you run the following two statements and confirm that no error returned?
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.
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 */