ORA-01861: literal does not match format string

Question

本帖最后由 jinlinmiaoyiyao 于 2011-12-07 17:34:00 编辑

Answer 1

select w.FlowInTime,length(w.FlowInTime),to_date(subStr(w.FlowInTime,0,10),'YYYY-MM-DD'),length(to_date(subStr(w.FlowInTime,0,10),'YYYY-MM-DD')),length(to_date('2011-10-31','YYYY-MM-DD')),
w1.FlowID, w1.BusinessNo, w1.ModelNo, smm.ModelName, w1.NodeNo, w1.NodeName, w2.deptcode as DeptCodeBranch, w2.deptname as DeptNameBranch, w2.operatorcode as OperatorCodeBranch, w2.operatorname as OperatorNameBranch, w2.submittime as SubmitTimeBranch, w1.OperatorCode, w1.OperatorName, w.SubmitTime 
from business.Wflog w, business.Swfnode s, business.Wflog w1, business.Swfmodelmain smm, business.Wflog w2 
where w.classcode='05' 
and w.modelno = s.modelno 
and w.nodeno = s.nodeno 
and s.endflag = 1 
and w.flowid = w1.flowid 
and w.logno = w1.logno+1 
--and w1.DeptCode like '200%' 
and w1.modelno = smm.modelno 
and w.flowid = w2.flowid 
and w.logno = w2.logno+2 
and to_date(subStr(w.FlowInTime,0,10),'YYYY-MM-DD') between to_date('2011-10-01','YYYY-MM-DD') and to_date('2011-10-31','YYYY-MM-DD') 重发一下SQL，刚才没写到代码框里面。

Answer 2

发了一天都没有人回答，是不屑回答还是人气凋零?问题已解决，原因是Wflow.FlowInTime存在垃圾数据13 236050020090903111154277 7699-09-03 11:43:35
14 234030020080123163347895 75675-01-23 00:00:0
15 223060020080317163302844 626208-01-01 00:00:
16 233860020070622161130375 593-06-22 16:15:36.
17 232000020070620161140531 531531-06-20 16:11:以上垃圾数据在转换为date的时候出错（空值不影响）在无法解决垃圾数据的前提下直接比较char值。
and substr(w.FlowInTime,0,10)>='2011-12-01'