ORA-01841(完整)年份必须介于-4713和+999之间，并且不能为0

Question

我使用这个查询来确定哪个工作在哪个班次完成。

SELECT srvb.kod AS code,
CASE
WHEN srvb.rectarih1 || ' ' || srvb.rectime1 >= srvb.rectarih1 || ' ' || '07:30' 
AND srvb.rectarih1 || ' ' || srvb.rectime1 <  srvb.rectarih1 || ' ' || '15:30' THEN
1
WHEN srvb.rectarih1 || ' ' || srvb.rectime1 >= srvb.rectarih1 || ' ' || '15:30' 
AND srvb.rectarih1 || ' ' || srvb.rectime1 <  srvb.rectarih1 || ' ' || '23:30' THEN
2
ELSE
3
END AS shift,
srvb.rectarih1 || ' ' || srvb.rectime1 AS full_time,
srvb.rectarih1 AS job_start_date,
srvb.rectime1
FROM srvb00O srvb
LEFT JOIN srvbp0 srvp
ON srvp.kod = srvb.operatorkodu
LEFT JOIN srvb00 srv0
ON srv0.kod = srvb.kod
LEFT JOIN srvbc0 srvc
ON srvc.kod = srv0.cihazkartno
WHERE srvb.rectarih1 <> '          '
AND srvb.rectime1  <> '          '
AND srvb.rectarih2 <> '          '
AND srvb.rectime2  <> '          '
-- AND sysdate-TO_DATE(srvb.rectarih1,'YYYY/MM/DD')=1
ORDER BY srvb.kod DESC

这是清单的某些部分。

CODE
0000006182		0000006182		0000006181		0000006181		0000006180		0000006180		0000006179		0000006179		0000006178		0000006178		0000006175		0000006175		0000006174		0000006173		0000006173		0000006170		0000006170		0000006169		0000006169		0000006168		0000006168		0000006167		0000006167		0000006166		0000006166		0000006165		0000006165		0000006164		0000006164		0000006163		0000006163		0000006162		0000006162		0000006161		0000006161		0000006160		0000006160		0000006158		0000006158		0000006157		0000006157		0000006156		0000006156		0000006155		0000006154		0000006154		0000006151		0000006150		0000006148		0000006148		0000006147		0000006147		0000006146		0000006146		0000006145		0000006143		0000006142		0000006141		0000006141		0000006140		0000006140		0000006139		0000006139		0000006138		0000006138		0000006137		0000006137		0000006136		0000006136		0000006134		0000006132		0000006132		0000006131		0000006131		0000006130		0000006130		0000006129		0000006127		0000006127		0000006126		0000006125		0000006124		0000006124		0000006123		0000006123		0000006122		0000006122		0000006121		0000006121		0000006120		0000006120		0000006119		0000006118		0000006118		0000006117		0000006117		0000006116		0000006116		0000006113		0000006113		0000006112		0000006112		0000006111		0000006111		0000006110		0000006110		0000006109		0000006108		0000006108		0000006107		0000006106		0000006105		0000006105		0000006104		0000006104		0000006103		0000006103		0000006102		0000006102		0000006101		0000006101		0000006099		0000006098		0000006097		0000006096
0000006095		0000006094		0000006094		0000006093		0000006093		0000006092		0000006092		0000006091		0000006091		0000006090		0000006090		0000006086		0000006086		0000006085		0000006085		0000006084		0000006084		0000006083		0000006083		0000006082		0000006082		0000006081		0000006081		0000006077
0000006076		0000006075		0000006074		0000006074		0000006072		0000006072		0000006071		0000006070		0000006070		0000006068		0000006068		0000006067
0000006066
0000006065		0000006064		0000006063		0000006062		0000006061		0000006060		0000006058		0000006058		0000006057

您可能在SRVB.RECTARIH1中有一些负值。(可能是伪数据)

检查导致RECTARIH1字段(..WHERE RECTARIH1<0)过滤错误的表

只是为了重新生成错误；

select TO_DATE ('-4714', 'YYYY') as t from dual;
"ORA-01841:"(full) year must be between -4713 and +9999, and not be 0" Cause: Illegal year entered Action: Input year in the specified range"