select * from ROAAGT0 where TRAN1 is null and RDATE is not null and (
to_date(ENDTIME||':'||ENDHOUR||':'||ENDMINUTE,'YYYY-MM-DD:HH24:MI')>= sysdate and to_date(STARTTIME||':'||STARTHOUR||':'||STARTMINUTE,'YYYY-MM-DD:HH24:MI')<= sysdate
or
ENDTIME is null and STARTTIME is null) 报错,ORA-01841: (full) year must be between -4713 and +9999, and not be 0
如果去掉中间3行,像下面这样就没问题,上面该怎样修改才行? select * from ROAAGT0 where TRAN1 is null and RDATE is not null and (
ENDTIME is null and STARTTIME is null)
to_date('2010-11-21 00:00:00','yyyy-mm-dd hh24:mi:ss'),
(ENDTIME is null and STARTTIME is null) or
(to_date(ENDTIME||':'||ENDHOUR||':'||ENDMINUTE,'YYYY-MM-DD:HH24:MI')>= sysdate and
to_date(STARTTIME||':'||STARTHOUR||':'||STARTMINUTE,'YYYY-MM-DD:HH24:MI')<= sysdate )
)还有你这个to_date方法我不是很清楚,是否需要对to_date(null)值做特殊处理
和
ENDTIME is null and STARTTIME is null
这两个不是相对独立的吗?我需要用所有时间,就输入一条开始结束时间全是空代表全部时间,和ENDTIME is null and STARTTIME is null对应了,为什么还不行?