Solution for Error casting varchar to decimal even though SQL query doesn’t return any results
is Given Below:
I have a SQL query which fetches no rows. I am using cast function in select statement to cast from varchar to decimal, I am getting exception saying couldn’t cast varchar to decimal.
This is my query
select distinct order_id, order_amt, cast(replace(order_amt, ',', '') as decimal(11, 2)) as amt from table1 left join table2 on table1.order_id = table2.order_num where table2.order_num is null and table1.order_id not in (123, 456)
Not sure why I am getting error, even though query returns no results.
Any inputs are highly appreciated.
You don’t have control over where SQL Server decides to evaluate expressions. So, expressions might be evaluated (and generate an error), even on rows that are filtered out.
In this case, there is a very simply solution:
Try_Cast(Replace(order_amt, ',', '') as decimal(11,2)) as amt