Hi Everyone,

I'm trying to load date/time data into a table. The data is formatted in one of two ways:
1. Exact Date/Time
2. A day start/end columns.

Oracle is rejecting all records that don't use the exact date/time format. I've specified Default NULL in the create table statement, but it seems to be ignored. What am I doing wrong?



--Create Table statement
CREATE TABLE TIMES
(
PRIME_ID NUMBER(17) not null,
A_SEQ_NO NUMBER(10),
STARTDATE DATE DEFAULT null,
ENDDATE DATE DEFAULT null,
DAY_START NUMBER(2),
DAY_END NUMBER(2)
);



--Control File and example data
unrecoverable load data
infile *
append
into table TIMES
(
PRIME_ID position (1:1) integer external,
A_SEQ_NO position (2:3) integer external,
STARTDATE position (5:23) date "yyyy-mm-dd hh24:mi:ss",
ENDDATE position (25:43) date "yyyy-mm-dd hh24:mi:ss",
DAY_START position (45:46) integer external,
DAY_END position (48:49) integer external
)

begindata
5 1 0 0
5 2 4 5
6 1 0 0
6 2 5 9
6 3 0 0
8 1985.01.01 00:00:00 2001.12.04 00:00:00
(EDIT COMMENT: The data is not being displayed correctly, but the data is in the correct position. I.E. No overlapping data.)

--Error Messages
Error Message:
Record 1: Rejected - Error on table T_RC_TIMES, column STARTDATE
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Record 2: Rejected - Error on table T_RC_TIMES, column STARTDATE
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


[Edited by pfc on 10-08-2001 at 04:53 AM]