SQL语句报错 在线等 急~~~~~~~~~~~~~~~~~~~

[Antony110]

select a.ncrb_no, a.matl_tp, a.matl_gb, case a.iqc_stat when '1' then 'Approval Waiting' when '2' then 'drafting/decision' 
        when '3' then 'approval Complete' when '4' then 'Return' else 'approval Wait' end iqc_stat, a.iqc_stat iqc_stat2, 
        a.matl_cd, c.matl_nm ,   a.iqc_no , 
        z.user_name, 
        substring(a.iqc_no,1,4)+substring(a.iqc_no,6,8) iqc_no2, isnull(f.incom_no,'') incom_no,    
        substring(a.recv_dt,1,4)+'/'+substring(a.recv_dt,5,2)+'/'+   
        substring(a.recv_dt,7,2) recv_dt,   
        a.incom_qty, d.unit, 
        substring(a.result_dtm,1,4)+'/'+substring(a.result_dtm,5,2)+'/'+substring(a.result_dtm,7,2) result_dt,  
        substring(a.result_dtm,9,2)+':'+substring(a.result_dtm,11,2)+':'+substring(a.result_dtm,13,2) result_tm,  
        b.vend_nm, 
        case  when a.result = 'A' then 'Accept' when a.result = 'R' and a.re_judge = 'Y' then 'Reject [Waive]' when a.result = 'R' and a.re_judge is null then 'Reject'  when a.result = 'W' then 'Waive' else null end result_nm,    
        substring(a.life_time,1,4)+'/'+substring(a.life_time,5,2)+'/'+   
        substring(a.life_time,7,2) life_time, a.iqc_insp_id, 
       e.user_name iqc_insp_nm,  
       a.spec_tp, a.mgrp_cd, a.vend_cd ,a.result, 
         case isnull(y.veri_nm, 'NULL') when 'NULL' then case x.defect_cn when 0 then null else (case when a.result = 'A' then '' else x.para_nm end) end   else y.veri_nm end para_nm, case when a.result = 'R' then x.defect_cn else '0' end defect_cn,  
         sign( convert( int, getdate(), 112) - convert(int, convert( datetime, life_time, 112 ), 112 ) ) over_due, 
         f.urgent, 
         a.remarks, 
         case 
           when t3.ulm_gb='U' then cast(t3.u_qty as varchar)+' '+ t3.u_unit+' / '+ 
             case 
               when t3.u_ls_gb='S' then 'Shipment' 
               else 'Lot' 
             end 
           when t3.ulm_gb='L' then 'LTPD '+t3.l_ratio+'% C='+t3.l_cnt 
           when t3.ulm_gb='M' then t3.m_insp_lvl+', '+t3.m_insp_gb+'/'+t3.m_insp_mth+', AQL '+t3.aql+'%' 
         end sam_rule 
   from iqc_iqc_mst a LEFT OUTER JOIN  (select a.iqc_no, max(a.defect_cn) defect_cn, 
                substring(max(str(isnull(a.defect_cn, 0), 10)+a.para_cd), 11, 8) para_cd,  
                substring(max(str(isnull(a.defect_cn, 0), 10)+b.para_nm), 11, 20) para_nm      
           from iqc_calc_result a,                                                         
                iqc_para b                                                                 
          where b.para_cd = a.para_cd                                                      
       group by a.iqc_no) x   ON    x.iqc_no = a.iqc_no                    
        LEFT OUTER JOIN  (select distinct a.iqc_no, b.veri_nm                                               
           from iqc_veri_result a, iqc_veri b                                              
          where b.veri_cd = a.veri_cd                                                      
            and a.result = 'R'                                                             
        ) y  ON y.iqc_no = a.iqc_no                                        
         LEFT OUTER JOIN iqc_req_insp f ON f.iqc_no = a.iqc_no            
         LEFT OUTER JOIN 
        (select t1.Iqc_no, t1.ulm_gb, t1.u_qty, t1.u_unit, t1.u_ls_gb, t1.l_ratio, t1.m_insp_lvl, t1.m_insp_gb, t1.m_insp_mth, t1.aql, t1.l_cnt 
         from iqc_insp_size t1, iqc_pgrp t2 
         where t1.pgrp_cd=t2.pgrp_cd 
         and t2.Visual_yn='1' 
        ) t3 on t3.Iqc_no=a.iqc_no, 
        (select a.user_name, b.iqc_no                          
        from qis_user a, iqc_apprv b                           
        where a.user_id = b.user_id                             
           and b.odr = 0)z  ,   
       iqc_vendor b, iqc_vendor_matl c,iqc_matl d,  
       qis_user e    
 where a.result_dtm between case a.loc_cd when 'H' then N'20120324070000' else N'20120324070000' end 
 and case a.loc_cd when 'H' then N'20120325065959'   else N'20120325065959' end 
 and a.loc_cd in  ('H') 
 and b.vend_cd = a.vend_cd  
 and a.mgrp_cd = N'CA' 
 and a.matl_gb in ('P') 
 and a.result in (N'A', N'R', N'W') 
 and c.matl_cd = a.matl_cd 
 and c.vend_cd = a.vend_cd 
 and a.matl_cd = d.matl_cd 
 and e.user_id = a.iqc_insp_id   
 and z.iqc_no = a.iqc_no    
 order by a.iqc_no  

报错：The conversion of a nvarchar data type to a datetime data type resulted in an out of range value
哪位大哥能帮我分析下原因？不胜感谢

[AcHerat]

convert( datetime, life_time, 112 )

。。。

[jwdream2008]

从字符类型到时间类型转换出错，超出范围！！！

[jwdream2008]

检查你要转换的字符串是否有不合法的数据（无法转换为时间类型的字符串）！！！

[开着拖拉机泡妞]

超出范围了

SQL Server 2005 对数据类型使用以下优先级顺序（先高后低）：

不带世纪数位 (yy)

带世纪数位 (yyyy)

标准

输入/输出-

0 或 100

默认设置mon dd yyyy hh:miAM（或 PM）1 101

美国

mm/dd/yyyy 2 102 ANSI yy.mm.dd 3103

英国/法国

dd/mm/yy 4 104

德国

dd.mm.yy 5 105

意大利

dd-mm-yy 120

ODBC 规范

yyyy-mm-ddhh:mi:ss(24h)

[Antony110]

我把最后2个条件

 and e.user_id = a.iqc_insp_id   
 and z.iqc_no = a.iqc_no  

注释掉之后就可以了
但是放着又不行了？
原来是可以的
不知道什么数据有问题导致的

[Antony110]

急啊急

[开着拖拉机泡妞]

[Quote=引用 6 楼 的回复:]

急啊急
[/Quote]你从新把语句整理一下吧。想必这样的语句效率也不会怎么好。顺便优化一下