ORA-01841 (full) year must be between -4713 and +9999, and not be 0

Viewed 42

I am using this query to determine which job is done in which shift.

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

Here is the some part of the list.

CODE SHIFT FULL_TIME JOB_START_DATE RECTIME1
0000006182 3 2022/09/18 01:00 2022/09/18 01:00
0000006182 3 2022/09/18 01:00 2022/09/18 01:00
0000006181 3 2022/09/18 02:00 2022/09/18 02:00
0000006181 3 2022/09/18 02:00 2022/09/18 02:00
0000006180 3 2022/09/18 06:40 2022/09/18 06:40
0000006180 3 2022/09/18 06:40 2022/09/18 06:40
0000006179 3 2022/09/18 02:00 2022/09/18 02:00
0000006179 3 2022/09/18 02:00 2022/09/18 02:00
0000006178 3 2022/09/18 06:00 2022/09/18 06:00
0000006178 3 2022/09/18 06:00 2022/09/18 06:00
0000006175 1 2022/09/17 08:00 2022/09/17 08:00
0000006175 1 2022/09/17 08:00 2022/09/17 08:00
0000006174 1 2022/09/17 08:00 2022/09/17 08:00
0000006173 3 2022/09/17 00:20 2022/09/17 00:20
0000006173 3 2022/09/17 00:20 2022/09/17 00:20
0000006170 3 2022/09/16 03:00 2022/09/16 03:00
0000006170 3 2022/09/16 03:00 2022/09/16 03:00
0000006169 3 2022/09/16 02:00 2022/09/16 02:00
0000006169 3 2022/09/16 02:00 2022/09/16 02:00
0000006168 3 2022/09/16 01:00 2022/09/16 01:00
0000006168 3 2022/09/16 01:00 2022/09/16 01:00
0000006167 2 2022/09/15 21:00 2022/09/15 21:00
0000006167 2 2022/09/15 21:00 2022/09/15 21:00
0000006166 2 2022/09/15 20:30 2022/09/15 20:30
0000006166 2 2022/09/15 20:30 2022/09/15 20:30
0000006165 2 2022/09/15 18:15 2022/09/15 18:15
0000006165 2 2022/09/15 18:15 2022/09/15 18:15
0000006164 2 2022/09/15 17:00 2022/09/15 17:00
0000006164 2 2022/09/15 17:00 2022/09/15 17:00
0000006163 2 2022/09/15 16:00 2022/09/15 16:00
0000006163 2 2022/09/15 16:00 2022/09/15 16:00
0000006162 1 2022/09/15 08:00 2022/09/15 08:00
0000006162 1 2022/09/15 08:00 2022/09/15 08:00
0000006161 1 2022/09/16 08:00 2022/09/16 08:00
0000006161 1 2022/09/16 08:00 2022/09/16 08:00
0000006160 1 2022/09/16 09:00 2022/09/16 09:00
0000006160 1 2022/09/16 09:00 2022/09/16 09:00
0000006158 3 2022/09/15 03:00 2022/09/15 03:00
0000006158 3 2022/09/15 03:00 2022/09/15 03:00
0000006157 3 2022/09/15 01:00 2022/09/15 01:00
0000006157 3 2022/09/15 01:00 2022/09/15 01:00
0000006156 3 2022/09/15 00:20 2022/09/15 00:20
0000006156 3 2022/09/15 00:20 2022/09/15 00:20
0000006155 1 2022/09/14 08:00 2022/09/14 08:00
0000006154 2 2022/09/14 16:00 2022/09/14 16:00
0000006154 2 2022/09/14 16:00 2022/09/14 16:00
0000006151 1 2022/09/14 09:30 2022/09/14 09:30
0000006150 1 2022/09/14 08:00 2022/09/14 08:00
0000006148 2 2022/09/13 22:00 2022/09/13 22:00
0000006148 2 2022/09/13 22:00 2022/09/13 22:00
0000006147 2 2022/09/13 20:00 2022/09/13 20:00
0000006147 2 2022/09/13 20:00 2022/09/13 20:00
0000006146 2 2022/09/13 18:00 2022/09/13 18:00
0000006146 2 2022/09/13 18:00 2022/09/13 18:00
0000006145 1 2022/09/13 14:30 2022/09/13 14:30
0000006143 1 2022/09/13 11:00 2022/09/13 11:00
0000006142 1 2022/09/13 08:00 2022/09/13 08:00
0000006141 1 2022/09/13 08:00 2022/09/13 08:00
0000006141 1 2022/09/13 08:00 2022/09/13 08:00
0000006140 2 2022/09/12 20:00 2022/09/12 20:00
0000006140 2 2022/09/12 20:00 2022/09/12 20:00
0000006139 2 2022/09/12 18:30 2022/09/12 18:30
0000006139 2 2022/09/12 18:30 2022/09/12 18:30
0000006138 2 2022/09/12 17:00 2022/09/12 17:00
0000006138 2 2022/09/12 17:00 2022/09/12 17:00
0000006137 1 2022/09/12 14:00 2022/09/12 14:00
0000006137 1 2022/09/12 14:00 2022/09/12 14:00
0000006136 1 2022/09/12 09:00 2022/09/12 09:00
0000006136 1 2022/09/12 09:00 2022/09/12 09:00
0000006134 1 2022/09/12 08:00 2022/09/12 08:00
0000006132 3 2022/09/11 02:00 2022/09/11 02:00
0000006132 3 2022/09/11 02:00 2022/09/11 02:00
0000006131 3 2022/09/11 00:45 2022/09/11 00:45
0000006131 3 2022/09/11 00:45 2022/09/11 00:45
0000006130 3 2022/09/10 00:00 2022/09/10 00:00
0000006130 3 2022/09/10 00:00 2022/09/10 00:00
0000006129 1 2022/09/10 08:00 2022/09/10 08:00
0000006127 1 2022/09/10 09:30 2022/09/10 09:30
0000006127 1 2022/09/10 09:30 2022/09/10 09:30
0000006126 1 2022/09/10 10:30 2022/09/10 10:30
0000006125 3 2022/09/10 06:00 2022/09/10 06:00
0000006124 3 2022/09/10 05:15 2022/09/10 05:15
0000006124 3 2022/09/10 05:15 2022/09/10 05:15
0000006123 3 2022/09/10 04:00 2022/09/10 04:00
0000006123 3 2022/09/10 04:00 2022/09/10 04:00
0000006122 3 2022/09/10 02:00 2022/09/10 02:00
0000006122 3 2022/09/10 02:00 2022/09/10 02:00
0000006121 3 2022/09/10 01:30 2022/09/10 01:30
0000006121 3 2022/09/10 01:30 2022/09/10 01:30
0000006120 3 2022/09/10 00:30 2022/09/10 00:30
0000006120 3 2022/09/10 00:30 2022/09/10 00:30
0000006119 3 2022/09/10 00:00 2022/09/10 00:00
0000006118 1 2022/09/09 07:40 2022/09/09 07:40
0000006118 1 2022/09/09 07:40 2022/09/09 07:40
0000006117 3 2022/09/09 06:00 2022/09/09 06:00
0000006117 3 2022/09/09 06:00 2022/09/09 06:00
0000006116 3 2022/09/09 05:30 2022/09/09 05:30
0000006116 3 2022/09/09 05:30 2022/09/09 05:30
0000006113 3 2022/09/09 04:00 2022/09/09 04:00
0000006113 3 2022/09/09 04:00 2022/09/09 04:00
0000006112 3 2022/09/09 02:00 2022/09/09 02:00
0000006112 3 2022/09/09 02:00 2022/09/09 02:00
0000006111 3 2022/09/09 01:00 2022/09/09 01:00
0000006111 3 2022/09/09 01:00 2022/09/09 01:00
0000006110 3 2022/09/09 00:00 2022/09/09 00:00
0000006110 3 2022/09/09 00:00 2022/09/09 00:00
0000006109 1 2022/09/08 15:00 2022/09/08 15:00
0000006108 1 2022/09/08 08:00 2022/09/08 08:00
0000006108 1 2022/09/08 08:00 2022/09/08 08:00
0000006107 1 2022/09/08 09:00 2022/09/08 09:00
0000006106 1 2022/09/08 11:00 2022/09/08 11:00
0000006105 3 2022/09/08 06:00 2022/09/08 06:00
0000006105 3 2022/09/08 06:00 2022/09/08 06:00
0000006104 3 2022/09/08 05:00 2022/09/08 05:00
0000006104 3 2022/09/08 05:00 2022/09/08 05:00
0000006103 3 2022/09/08 03:30 2022/09/08 03:30
0000006103 3 2022/09/08 03:30 2022/09/08 03:30
0000006102 3 2022/09/08 02:00 2022/09/08 02:00
0000006102 3 2022/09/08 02:00 2022/09/08 02:00
0000006101 3 2022/09/08 01:00 2022/09/08 01:00
0000006101 3 2022/09/08 01:00 2022/09/08 01:00
0000006099 2 2022/09/07 16:00 2022/09/07 16:00
0000006098 1 2022/09/07 10:00 2022/09/07 10:00
0000006097 1 2022/09/08 11:00 2022/09/08 11:00
0000006096 1 2022/09/07 09:30 2022/09/07 09:30
0000006095 1 2022/09/07 10:30 2022/09/07 10:30
0000006094 3 2022/09/07 05:00 2022/09/07 05:00
0000006094 3 2022/09/07 05:00 2022/09/07 05:00
0000006093 3 2022/09/07 04:15 2022/09/07 04:15
0000006093 3 2022/09/07 04:15 2022/09/07 04:15
0000006092 3 2022/09/07 03:00 2022/09/07 03:00
0000006092 3 2022/09/07 03:00 2022/09/07 03:00
0000006091 3 2022/09/07 02:00 2022/09/07 02:00
0000006091 3 2022/09/07 02:00 2022/09/07 02:00
0000006090 2 2022/09/06 21:30 2022/09/06 21:30
0000006090 2 2022/09/06 21:30 2022/09/06 21:30
0000006086 3 2022/09/06 06:10 2022/09/06 06:10
0000006086 3 2022/09/06 06:10 2022/09/06 06:10
0000006085 3 2022/09/06 05:00 2022/09/06 05:00
0000006085 3 2022/09/06 05:00 2022/09/06 05:00
0000006084 3 2022/09/06 01:45 2022/09/06 01:45
0000006084 3 2022/09/06 01:45 2022/09/06 01:45
0000006083 3 2022/09/06 23:30 2022/09/06 23:30
0000006083 3 2022/09/06 23:30 2022/09/06 23:30
0000006082 2 2022/09/05 20:30 2022/09/05 20:30
0000006082 2 2022/09/05 20:30 2022/09/05 20:30
0000006081 2 2022/09/05 21:00 2022/09/05 21:00
0000006081 2 2022/09/05 21:00 2022/09/05 21:00
0000006077 1 2022/09/05 12:30 2022/09/05 12:30
0000006076 1 2022/09/05 10:00 2022/09/05 10:00
0000006075 1 2022/09/05 14:00 2022/09/05 14:00
0000006074 1 2022/09/05 09:00 2022/09/05 09:00
0000006074 1 2022/09/05 09:00 2022/09/05 09:00
0000006072 2 2022/09/06 16:00 2022/09/06 16:00
0000006072 2 2022/09/06 16:00 2022/09/06 16:00
0000006071 1 2022/09/03 10:00 2022/09/03 10:00
0000006070 2 2022/09/02 15:30 2022/09/02 15:30
0000006070 2 2022/09/02 15:30 2022/09/02 15:30
0000006068 2 2022/09/02 18:00 2022/09/02 18:00
0000006068 2 2022/09/02 18:00 2022/09/02 18:00
0000006067 1 2022/09/02 14:15 2022/09/02 14:15
0000006066 1 2022/09/02 13:00 2022/09/02 13:00
0000006065 1 2022/09/02 10:00 2022/09/02 10:00
0000006064 1 2022/09/02 10:30 2022/09/02 10:30
0000006063 1 2022/09/02 10:00 2022/09/02 10:00
0000006062 1 2022/09/02 08:15 2022/09/02 08:15
0000006061 1 2022/09/02 09:30 2022/09/02 09:30
0000006060 1 2022/09/03 10:00 2022/09/03 10:00
0000006058 2 2022/09/01 20:00 2022/09/01 20:00
0000006058 2 2022/09/01 20:00 2022/09/01 20:00
0000006057 2 2022/09/01 16:00 2022/09/01 16:00

Problem I am facing here is in WHERE statement.
When I try to filter the yesterday's data by using

sysdate-TO_DATE(srvb.rectarih1,'YYYY/MM/DD')=1

I am getting this error -->

"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"

1 Answers

You may have some negative values in SRVB.RECTARIH1. (Maybe dummy data )

Check table that causing error with filtering on RECTARIH1 field ( .. WHERE RECTARIH1 < 0 )

Just to regenerate error;

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"
Related