Moving a date between tables is causing error ORA-01841 Here's my script:

INSERT INTO BRAND_RECORD
SELECT 
    STRENGTH,
    GENERIC_NAME,
    TO_DATE(a1.FIRST_CLAIM_DATE) as INTERCHANGEABILITY_DATE 
FROM DRUG_INFO
INNER JOIN 
(
    SELECT
        FIRST_CLAIM_DATE, 
        SEQUENTIAL_ID 
    FROM DRUG_INFO 
    WHERE 
        SEQUENTIAL_ID IN 
        (
            SELECT MIN(SEQUENTIAL_ID)
            FROM 
            (
                SELECT * 
                FROM DRUG_INFO
                WHERE 
                    SEQUENTIAL_ID NOT IN 
                    (
                        SELECT MIN(SEQUENTIAL_ID) 
                        FROM DRUG_INFO
                        GROUP BY GENERIC_NAME, STRENGTH
                    )
            )
            GROUP BY GENERIC_NAME, STRENGTH
        )
) a1
    ON (a1.SEQUENTIAL_ID = DRUG_INFO.SEQUENTIAL_ID + 1);


Summary: Everything after INSERT INTO BRAND_RECORD works perfectly well. It is a query that produces a STRENGTH, GENERIC_NAME, and INTERCHANGEABILITY_DATE.
BRAND_RECORD is a table with the columns STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE.

However, I get the following error:


  Error report - SQL Error: ORA-01841: (full) year must be between -4713
  and +9999, and not be 0
  01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
  *Cause:    Illegal year entered
  *Action:   Input year in the specified range


This seems like some sort of conversion problem. But I'm selecting a date and putting it into a table that takes a date! Any idea what the problem might be?

UPDATE: I changed my default date format to MM/DD/YYYY HH24:MI:SS. I now get the following error when I try this:


  Error report - SQL Error: ORA-01858: a non-numeric character was found
  where a numeric was expected 01858. 00000 - "a non-numeric character
  was found where a numeric was expected" *Cause: The input data to be
  converted using a date format model was incorrect. The input data did
  not contain a number where a number was required by the format model.
  *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. Error
  starting at line : 1 in command - INSERT INTO BRAND_RECORD Error at
  Command Line : 1 Column : 24 Error report - SQL Error: ORA-00926:
  missing VALUES keyword 00926. 00000 - "missing VALUES keyword" *Cause:
  *Action:


Here are my create table statements for both tables:

CREATE TABLE BRAND_RECORD 
(
  GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL 
, INTERCHANGEABILITY_DATE DATE 
, STRENGTH VARCHAR2(25 BYTE) 
) 
LOGGING 
TABLESPACE DATAL03 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL;    

CREATE TABLE DRUG_INFO 
(
  DIN VARCHAR2(20 BYTE) NOT NULL 
, TRADE_NAME VARCHAR2(50 BYTE) NOT NULL 
, FORM VARCHAR2(40 BYTE) 
, STRENGTH VARCHAR2(25 BYTE) 
, GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL 
, MANUFACTURER VARCHAR2(100 BYTE) NOT NULL 
, CR_DATE DATE 
, NOC_DATE DATE 
, FIRST_CLAIM_DATE DATE 
, SEQUENTIAL_ID NUMBER NOT NULL 
) 
LOGGING 
TABLESPACE DATAL03 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL;
Zgguy answered this in comments:

Explicitly stating columns in the insert solved the problem, as it was trying to put columns in the wrong columns.

INSERT INTO BRAND_RECORD (STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE)