Date insert error...

Hello - I am getting the error "ORA-01843: not a valid month" during a simple table insert.

Our NLS_DATE_FORMAT is set to DD-MM-RR, however the existing dates in the tables
(recently converted from a Sybase database) are in a MM/DD/YYYY
HH:MM:SS format.

When I attempt to insert into a table with 2 date fields using SYSDATE I get the error.
If I attempt to convert SYSDATE to a string and then back to a DATE the error still occurs.

It seems that the SYSDATE is in a format of DD-MON-YY format when trying to insert
(eg. 23-JUL-07).

This is what I am getting:

getdates                   VARCHAR2(21);
insertdate                 DATE;

getdates := TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'); insertdate := TO_DATE(getdates, 'MM/DD/YYYY HH24:MI:SS');

dbms_output.put_line (SYSDATE);
dbms_output.put_line (getdates);
dbms_output.put_line (insertdate);

The output is:

23-JUL-07
07/23/2007 13:10:53
23-JUL-07 It seems the conversion into insertdate isn't working.

Any ideas why this might be failing?

Thanks

Jerry Received on Mon Jul 23 2007 - 15:14:20 CDT