TNCS01表中有两个符合日期格式的字符字段:JKM和BCM,但执行以下语句就报日期格式出错:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
该语句原来运行在程度中一直非常正常,并且在另一台数据结构完全一样的服务器中运行该语句执行成功。select * from (select * from TNCS01 where DMLB='NSSBBD' and DM='0101') A where to_date('2008.06.01','YYYY.MM.DD') between to_date(JKM,'YYYY.MM.DD') and
to_date(BCM,'YYYY.MM.DD')
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
该语句原来运行在程度中一直非常正常,并且在另一台数据结构完全一样的服务器中运行该语句执行成功。select * from (select * from TNCS01 where DMLB='NSSBBD' and DM='0101') A where to_date('2008.06.01','YYYY.MM.DD') between to_date(JKM,'YYYY.MM.DD') and
to_date(BCM,'YYYY.MM.DD')
JL_HM DMLB DM DMMC JKM BCM
811 NSSBBD 0101 DSB011001 1900.01.01 2222.12.31
812 NSSBBD 0101 DSB012001 1900.01.01 2222.12.31
813 NSSBBD 0101 DSB010001 1900.01.01 2222.12.31
814 NSSBBD 0101 DSB015001 1900.01.01 2222.12.31
815 NSSBBD 0101 DSB016001 1900.01.01 2222.12.31
816 NSSBBD 0101 DSB017001 1900.01.01 2222.12.31
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 感觉是嵌套层数超过三层后才出现这个奇怪的错误select DM,DMMC from
(
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') as SSSQ_Q,to_date(BCM,'YYYY.MM.DD') as SSSQ_Z from
TNCS01 where DMLB='NSSBBD' and dm in
(
select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null)
)
)
TNCS01 where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q and SSSQ_Z
JKM,BCM本来就是date,用to_char()应该就不会报错了
而你加了另一个条件之后, 可能第一页就扫描到错误的记录了.
所以中间两层不报错,而第三层报错。