以下是从数据类型 varchar 转换为 numeric 时出错”，不知道错在哪了，求助

Question

需要用sql server 数据库：如果某种中药的库存数量为1000，当日出库数量为100，需要计算可用天数即1000/100=10，并根据这个天数正序排列，当库存数量为0或者当日出库数量为0，则在天数处显示没有库存或者当日未出库。可是当我用case判断在库存数量与当日出库数量都不为0的时候做除法运算的时候出现“从数据类型 varchar 转换为 numeric 时出错”。不知道错在哪了，求大神指教。代码如下：（注：a.num也就是kcyp.[库存数量]的数据类型是numeric(18, 5)，b.cknum也就是db_data.[数量]的数据类型也是numeric(18, 5)）

select a.*,b.*
,
case when a.num=0 then '库存为0，不能做除法'
when b.cknum IS null then '当天无取出，无法计算天数'
when a.num<>0 and b.cknum<>0 then cast(a.num as numeric(18, 5))/cast(b.cknum as numeric(18, 5))end
as tianshu

from
(SELECT ypwc.[名称] as y_name,ypwc.[编号] as bianhao,ypwc.[药品类型] as ytype,ypwc.[规格] as guige,ypwc.[药品单位] as danwei,kcyp.[仓库编号] as cangku,kcyp.[库存数量] as num FROM [T库存药品] as kcyp right join [T药品卫材] as ypwc on kcyp.[药品编号]=ypwc.[编号] where kcyp.[仓库编号]='7' and ypwc.[药品类型]='中药') as a
left join
(select sum(db_data.[数量]) as cknum,db_data.[编号] as bianhaob from [D调拨单] as db_dan inner join [D调拨数据] as db_data on db_dan.[单据编号]=db_data.[单据编号] where db_dan.[过单日期]>='2014-03-05 00:00:00' and db_dan.[过单日期]<='2014-03-05 23:59:59' group by db_data.[编号]) as b
on
a.bianhao = b.bianhaob

order by tianshu asc

Answer 1

cast(a.num as numeric(18, 5))/cast(b.cknum as numeric(18, 5) 这个得到的是数值类型，而你前面两个得到的是字符类型，我认为只要把这个直接转字符就可以了：to_char(a.num/b.cknum, $99999.99)

Answer 2

一个小建议，转换前用ISNUMERIC()函数试一下，就可以保证不出错了，CASE WHEN ISNUMERIC(123) = 1 THEN CAST(.. AS INT) ELSE NULL END