ORA-01870 trying to convert from date into timestamp with time zone

Question

when the following PL/SQL anonymous block is run using SQL DEVELOPER on Oracle DB 11g

SET SERVEROUTPUT ON;

DECLARE
    d_next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
    dbms_scheduler.evaluate_calendar_string(
            calendar_string => 'FREQ=DAILY;INTERVAL=15', 
            start_date => TO_TIMESTAMP_TZ('04/06/2021', 'DD/MM/YYYY'),
            return_date_after => TO_TIMESTAMP_TZ('01/01/2022', 'DD/MM/YYYY'),
            next_run_date => d_next_run_date
            );

    dbms_output.put_line(d_next_run_date);
END;

the following exception is thrown

ORA-01870: the intervals or datetimes are not mutually comparable
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3599
ORA-06512: at line 4
01870. 00000 -  "the intervals or datetimes are not mutually comparable"
*Cause:    The intervals or datetimes are not mutually comparable.
*Action:   Specify a pair of intervals or datetimes that are mutually
           comparable.

I suspect the issue is related to timezone.

The exception is not thrown if the arguments are changed to the following (notice that I changed the value that is passed into parameter return_date_after)

SET SERVEROUTPUT ON;

DECLARE
    d_next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
    dbms_scheduler.evaluate_calendar_string(
            calendar_string => 'FREQ=DAILY;INTERVAL=15', 
            start_date => TO_TIMESTAMP_TZ('04/06/2021', 'DD/MM/YYYY'),
            return_date_after => TO_TIMESTAMP_TZ('01/01/2021', 'DD/MM/YYYY'),
            next_run_date => d_next_run_date
            );

    dbms_output.put_line(d_next_run_date);
END;

or if the arguments are changed to the following (again I changed the value that is passed into parameter return_date_after)

SET SERVEROUTPUT ON;

DECLARE
    d_next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
    dbms_scheduler.evaluate_calendar_string(
            calendar_string => 'FREQ=DAILY;INTERVAL=15', 
            start_date => TO_TIMESTAMP_TZ('04/06/2021', 'DD/MM/YYYY'),
            return_date_after => TO_TIMESTAMP_TZ('01/06/2022', 'DD/MM/YYYY'),
            next_run_date => d_next_run_date
            );

    dbms_output.put_line(d_next_run_date);
END;

The requirements that I have to fullfill need not support timezone. If possible, I would like to use datatype DATE with procedure dbms_scheduler.evaluate_calendar_string. However, dbms_scheduler.evaluate_calendar_string parameters are of type TIMESTAMP WITH TIMEZONE. How could I convert from DATE into TIMESTAMP WITH TIMEZONE and avoid that exception?

I noticed that the exception is thrown when a date value that corresponds to summer is passed into parameter return_date_after. So the error might be related to daylight saving time.

database version: 11.2.0.1.0

nls parameters

What time zone is your session in (`select sessiontimezone from dual`); and what does `select TO_TIMESTAMP_TZ('01/01/2022', 'DD/MM/YYYY') from dual` return? — Alex Poole, Jul 28 '21 at 16:21
Try cast. `select cast (sysdate as timestamp with time zone) from dual;' — Belayer, Jul 28 '21 at 16:24
What Oracle version are you using? Mine is 12.2.0.1, and on my system your code runs perfectly fine; no error thrown, the procedure prints the next run date correctly. — , Jul 29 '21 at 14:23
However, I see that your two versions (original code and the one you say works) are identical. Did you make a mistake when you posted this - perhaps your code that throws an error is **not** the one you posted as the "existing code"? — , Jul 29 '21 at 14:25
It took me a while to spot that the second return-after date is 2021, while the failing one is 2022. Not sure why you'd use a date so far ahead of start date with a 15-day frequency. There's still no obvious reason for it to error though, and it's unlikely to matter how you generate the argument as it's failing within the package, not in your call to it. It looks like an 11g bug maybe. — Alex Poole, Jul 29 '21 at 15:16
@mathguy, I edited the post based on your comments. Actually the two versions are not identical, the value that is passed into parameter return_date_after is different. — Vitor Mira, Aug 01 '21 at 17:56

score 1 · Answer 1 · answered Aug 01 '21 at 18:14

I managed to avoid that error by adding time and timezone when casting the characters into TIMESTAMP WITH TIMEZONE

SET SERVEROUTPUT ON;

DECLARE
    d_next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
    dbms_scheduler.evaluate_calendar_string(
            calendar_string => 'FREQ=DAILY;INTERVAL=15', 
            start_date => TO_TIMESTAMP_TZ('04/06/2021 00:00:00 00:00', 'DD/MM/YYYY HH24:MI:SS TZH:TZM'),
            return_date_after => TO_TIMESTAMP_TZ('01/01/2022 00:00:00 00:00', 'DD/MM/YYYY HH24:MI:SS TZH:TZM'),
            next_run_date => d_next_run_date
            );

    dbms_output.put_line(d_next_run_date);
END;
/

output

15/01/22 00:00:00,000000 +00:00

ORA-01870 trying to convert from date into timestamp with time zone

1 Answers1