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!
What's wrong with convert function in where clause?
I have the query shown below. The "convert(smallmoney,newval
"Select Failed: 293 Cannot convert char value to smallmoney. The char value has incorrect syntax."
What's am I doing wrong?
"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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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 */
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 ABLEhttps://en.wikipedia.org/w/index.php?title=Sargable&oldid=573806804
...
Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
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.
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,new
or
select * from (
select * from table
where isnumeric(newValue) = 1
)
where convert(smallmoney,newValu
==============
this remains true:
avoid: and year(changeTime) >= 2014
replacement: and changeTime >= '20140101' /* >= 1st Jan 2014, no data conversion needed */
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,new
or
select * from (
select * from table
where isnumeric(newValue) = 1
)
where convert(smallmoney,newValu
==============
this remains true:
avoid: and year(changeTime) >= 2014
replacement: and changeTime >= '20140101' /* >= 1st Jan 2014, no data conversion needed */
Interesting, I have SQL Server 2014, but with try_convert(smallmoney,new
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.
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
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?
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)
Paul Maxwell
Vitor Montalvão
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.
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.Both queries give me:
Can you run the following two statements and confirm that no error returned?
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial