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.

Answer 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.

Answer 2

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;
/