Search code examples
oracleoracle11goracle10g

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 when using the lead function

I have been working on a query and couldn't figure out the solution for this error. Hope can get insights from the community on how to solve this:

select TO_TIMESTAMP(to_char(RTC_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') || ',' || lpad(rtc_event_order_seq, 3, '0'), 'YYYY-MM-DD HH24:MI:SS,FF3') AS TIME_STAMP,
TO_TIMESTAMP(to_char((Lead(RTC_TIMESTAMP,1)over(order by RTC_TIMESTAMP)), 'yyyy-mm-dd hh24:mi:ss') || ',' || lpad(rtc_event_order_seq, 3, '0'), 'YYYY-MM-DD HH24:MI:SS,FF3') AS NEXT_TIMESTAMP
from mytable

the Error I have been getting is:

ora-01841 full year must be between 4713 and 9999 and not be 0
Solution

  • The problem is with the final row in the result set, when there is no lead row to get values from. Lead(RTC_TIMESTAMP,1)over(order by RTC_TIMESTAMP) returns null, you concatenate a comma and milliseconds value. The next_timestamp calculation ends up as:

    TO_TIMESTAMP(',001', 'YYYY-MM-DD HH24:MI:SS,FF3')

    which is what throws that error. You can see that if you run the query without the to_timestamp() calls.

    The value is probably wrong anyway - presumably you should be looking at the lead rtc_event_order_seq as well - which would also be null, and trying to convert just ',' would also fail.

    You can wrap the next_timestamp is a case expression to avoid the issue:

    select TO_TIMESTAMP(to_char(RTC_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') || ',' || lpad(rtc_event_order_seq, 3, '0'), 'YYYY-MM-DD HH24:MI:SS,FF3') AS TIME_STAMP,
  case when Lead(RTC_TIMESTAMP,1)over(order by RTC_TIMESTAMP) is not null then
    TO_TIMESTAMP(to_char((Lead(RTC_TIMESTAMP,1)over(order by RTC_TIMESTAMP)), 'yyyy-mm-dd hh24:mi:ss')
      || ',' || lpad(Lead(rtc_event_order_seq,1)over(order by RTC_TIMESTAMP), 3, '0'), 'YYYY-MM-DD HH24:MI:SS,FF3')
    end AS NEXT_TIMESTAMP
from mytable
    TIME_STAMP NEXT_TIMESTAMP
    01-JAN-22 12.13.14.002000000 01-JAN-22 12.13.14.001000000
    01-JAN-22 12.13.14.001000000 null

    But rather than converting to and from strings, you can add multiples of one millisecond directly:

    select RTC_TIMESTAMP + (rtc_event_order_seq * interval '0.001' second) AS TIME_STAMP,
  case when Lead(RTC_TIMESTAMP, 1) over (order by RTC_TIMESTAMP) is not null then
    Lead(RTC_TIMESTAMP, 1) over (order by RTC_TIMESTAMP)
      + (Lead(rtc_event_order_seq, 1) over (order by RTC_TIMESTAMP) * interval '0.001' second)
    end AS NEXT_TIMESTAMP
from mytable
    TIME_STAMP NEXT_TIMESTAMP
    01-JAN-22 12.13.14.002000000 01-JAN-22 12.13.14.001000000
    01-JAN-22 12.13.14.001000000 null

    fiddle