i have third party data source has varchar(255) column contains numbers. column cost of item. able multiply column factor (2.3) default selling price cannot apply arithmetic operators on varchar column , cast function producing error when combined arithmetic operators.
this statement produces following sample results:
select averagecost dbo.inventory - 169
- 32.5
- 55.25
- 8.42
- 295
- 0
- 3.33333
this satatement:
select averagecost * 2.3 price dbo.iteminventory produces error
arithmetic overflow error converting data type numeric
writing statement follows cast decimal produces no errors adding arithmetic operator statement has eluded me, attempts have resulted in errors.
select cast(averagecost decimal(10, 2)) price dbo.inventory thanks in advance help.
if 2012+ use try_convert()
try_convert() catch conversion errors null
select try_convert(float,averagecost) * 2.3 price dbo.inventory returns
price 388.7 74.75 127.075 19.366 678.5 0 7.666659 to see values may causing conversion error
select * iteminventory try_convert(float,averagecost) null 
Comments
Post a Comment