Explain "ORA-01870: the intervals or datetimes are not mutually comparable"

Question

When this code is executed in SQL Developer against Oracle 11g I get an error,

begin
dbms_scheduler.create_job(
  job_name => 'comuni_34',
  job_type => 'plsql_block',
  job_action => 'begin com_auth_api.expire_old_passwords; end;',
  start_date => to_date('2009-jan-01 01:15:00', 'yyyy-mon-dd hh24:mi:ss'),
  repeat_interval => 'freq=daily',
  enabled => true,
  comments => 'Expire old passwords'
);
end;

This is the error,

Error starting at line 4 in command:
begin
dbms_scheduler.create_job(
  job_name => 'comuni_34',
  job_type => 'plsql_block',
  job_action => 'begin com_auth_api.expire_old_passwords; end;',
  start_date => to_date('2009-jan-01 01:15:00', 'yyyy-mon-dd hh24:mi:ss'),
  repeat_interval => 'freq=daily',
  enabled => true,
  comments => 'Expire old passwords'
);
end;
Error report:
ORA-01870: the intervals or datetimes are not mutually comparable
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 268
ORA-06512: at line 2
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.

A Google search did not help as it just listed loads of useless Oracle error code sites.

Maybe the source to SYS.DBMS_ISCHED/SYS.DBMS_SCHEDULER can explain this.

Update: A different job that uses '2010-apr-20 01:15:00' instead of '2009-jan-01 01:15:00' just worked maybe the problem is that dates that are too far in the past are not handled correctly.

Update: Using '2009-apr-01 01:15:00' instead of '2009-jan-01 01:15:00' just worked. However '2009-mar-01 01:15:00' did not work so there is limit one how far back a job can be started. Since I have solved my problem I cannot accept an answer that is a repeat of my solution but if someone wants to explain this further I will consider accepting that.

alternatively you could post your updates as an answer and accept that. The documentation doesn't specify a lower bound for the START_DATE and your explanation seems reasonable enough. — APC, Aug 31 '10 at 10:49

score 1 · Answer 1 · answered Aug 31 '10 at 10:48

1

I don't have 11g to test it but on a 10.2.0.4 database the CREATE_JOB was successful with START_DATE as early as 01-JAN-1970. It might be a bug and you may want to check on Metalink if you have access.

answered Aug 31 '10 at 10:48

score 1 · Answer 2 · answered Aug 31 '10 at 10:58

I think, you have the wrong set of NLS_LANG* parameters in your session. SQL Developer does it automaticly. Try this place at the beginnig of the script in sqlplus:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY= 'AMERICA';

So after that try to run:

begin
dbms_scheduler.create_job(
  job_name => 'comuni_34',
  job_type => 'plsql_block',
  job_action => 'begin com_auth_api.expire_old_passwords; end;',
  start_date => to_date('2009-jan-01 01:15:00', 'yyyy-mon-dd hh24:mi:ss'),
  repeat_interval => 'freq=daily',
  enabled => true,
  comments => 'Expire old passwords'
);
end;
/

I've just had the same error when creating a job, without setting any start_date at all from a box remote to the db. I tried on another session, logged in as oracle account on an instance node, and it worked, so I suspect an NLS-related issue. Need to confirm. — Joe Watkins, May 24 '14 at 19:11

score 1 · Answer 3 · answered Apr 22 '20 at 14:55

Thanks! This is the only relevant info about that problem I found, Google showed no results...

I faced a similar problem with SQL Developer 18.3.0.277 and Oracle 12c database.

It worked for me several times in the past, but suddenly it was showing that ora-01870 error.

I tried

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY= 'AMERICA';

but it didn't help.

What helped at the end was SQL Developer restart, really. Disconnect and connect or drop job/create job was not helping. The error was shown every time when I wanted to enable the job.

I made no alter session or similar before problem occurred. I simply disabled job and when I wanted to enable back again that error was shown.

score 0 · Answer 4 · answered Feb 14 '18 at 12:39

You might consider the behavior of function OVERLAPS. I don't know if Scheduler uses it, but the error message is the same:

SQL> select 1 from dual where (sysdate,sysdate+2) overlaps (sysdate+1,sysdate+5);

         1
----------
         1

SQL> select 1 from dual where (null,sysdate+2) overlaps (sysdate+1,sysdate+5);
         1
----------
         1

SQL> select 1 from dual where (sysdate+2,null) overlaps (sysdate+1,sysdate+5);
         1
----------
         1

SQL> select 1 from dual where (null,null) overlaps (sysdate+1,sysdate+5);
select 1 from dual where (null,null) overlaps (sysdate+1,sysdate+5)

ORA-01870: the intervals or datetimes are not mutually comparable

Explain "ORA-01870: the intervals or datetimes are not mutually comparable"

4 Answers4