从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界

sky1216520 2011-10-20 02:53:54

遇见的问题如题下面的是代码失效日期（sxrq）的字段已经非常详细的检查过了，的确没有错误的比如2月30号或者4月31这种的，实在是找不到问题了，请各位帮帮忙吧，a.dyfl='药品'或者非药品的时候都可以正常查询出来只有等于医疗器械的时候才会报题目上的错误。

select a.spid,a.spbh,a.spmch,a.tongym,a.shpgg,a.jixing,a.shengccj,a.dw,a.leibie,

b.shl,a.hshsj,a.hshjj,b.shl*a.hshjj as je ,b.pihao,b.sxrq,c.huowname,datediff(day,getdate(),

convert(datetime,case when substring(b.sxrq,6,2)='02' and len(b.sxrq)=7 then b.sxrq+'-01' 

                             when substring(b.sxrq,6,2)  in ('01','03','05','07','08','10','12') and len(b.sxrq)=7 then sxrq+'-01'

                             when substring(b.sxrq,6,2)  in ('04','06','09','11') and len(b.sxrq)=7 then b.sxrq+'-01' else b.sxrq end)) as 到期天数

from spkfk a join sphwph b on a.spid=b.spid 

              join huoweizl c on b.hw=c.hw

where b.shl>0 and 

(len(b.sxrq)=7 or len(b.sxrq)=10) and b.sxrq<>'' 

and b.sxrq is not null and (substring(b.sxrq,5,1)='-' or substring(sxrq,8,1)='-')

and (substring(b.sxrq,6,2)>='01' and substring(b.sxrq,6,2)<='12' or substring(b.sxrq,9,2)>='01' and substring(b.sxrq,9,2)<='31')

and  c.hwbh like '01' and c.duifbsh='ZDA' and a.dyfl='医疗器械'

order by b.sxrq,a.spbh,b.pihao

...全文