error converting varchar to float - how to bypass bad data
Posted on 2011-03-21
Im performing some calcuations within my select query.
Every now n then one of the values isnt entered correctly and is a varchar - this then returns an error from the stored proc and crashes my report.
heres the part of the query thats the problem:
dbo.tbPurchaseItem.mPrice * dbo.tbPurchaseItem.iQty - dbo.tbPurchaseItem.mPrice * dbo.tbPurchaseItem.iQty * (convert(float,dbo.tbPurchaseItem.vCustom1) /100) AS [cost inc discount],
dbo.tbPurchaseItem.vCustom1 was entered as 7,5 and crashed the report.
There are now checks to enure this wont happen again, however for older orders this may happen again..
Any ideas how to put a default value in or somehow change the varchar to float,