ORA-01843: not a valid month when inserting a date

Question

I am using Apex Oracle to run a script file merging data/tables with an existing schema. Here is the complete script file. The error is produced on Every Insert Command stating not a valid month. One error is produced on Alter command stating Column Type incompatible with referenced column type

The Script File:

--A1_rr_upd.txt file 

--dropping the table if already exists
DROP TABLE RRSTAFF;

--creating new table for RRSTAFF

CREATE TABLE RRSTAFF (
    staff_num CHAR(4) PRIMARY KEY,
    name VARCHAR(20),
    gender CHAR(1),
    date_join DATE,
    date_resign DATE,
    contact_num NUMBER(11),
    address VARCHAR(255)
);

--Adding new hired staff
INSERT INTO RRSTAFF VALUES ('s001','Adrian','M','2021/07/02',null,'60122000000','6 Jalan BU6, Petaling Jaya,Selangor');
INSERT INTO RRSTAFF VALUES ('s002','Jewel','F','2021/07/12',null,'60123000000','2 Jalan PJS2, Sunway,Selangor');
INSERT INTO RRSTAFF VALUES ('s003','Sean','M','2021/07/12',null,'60166000000','100 Sunway South K,Selangor');

--Adding new customer details
INSERT INTO rrcustomer VALUES ('1011','Dr','Brendan');
INSERT INTO rrcustomer VALUES ('1012','Dr','Haya');

--Adding new records into models
INSERT INTO model VALUES ('LGC83','LG C1 83 in OLED 4K TV', '500');
INSERT INTO model VALUES ('LGG77','LG Gallery 77 in OLED 4K TV', '400');
INSERT INTO model VALUES ('SNY43','Sony 43 in X75 4K Ultra HD Android TV', '200');
INSERT INTO model VALUES ('SHA50','Sharp 50 in  Full HD Basic TV ', '80');

--Adding new records into appliance
INSERT INTO appliance VALUES ('2010','LGC83','E',null);
INSERT INTO appliance VALUES ('2011','LGC83','E',null);

--Altering the HIRE table to link it with RRSTAFF using staff_id as Foriegn Key
ALTER TABLE
    hire ADD(
        staff_id VARCHAR(4),
            FOREIGN KEY (staff_id) REFERENCES rrstaff(staff_num)
    );

--Adding new hire records
INSERT INTO hire VALUES ('2010','2021/08/02','1011','2021/08/08','s001');
INSERT INTO hire VALUES ('2010','2021/08/22','1012','2021/08/28','s001');
INSERT INTO hire VALUES ('2011','2021/08/12','1013',null,'s001');

user_1144035 · Answer

In Oracle, you express date constants using the date keyword and a string in the ISO standard YYYY-MM-DD format. For instance:

INSERT INTO RRSTAFF
    VALUES ('s001', 'Adrian', 'M', DATE '2021-07-02', null, '60122000000', '6 Jalan BU6, Petaling Jaya,Selangor');

Here is a db<>fiddle.

user_10497483 · Answer

Use TO_DATE('2021/07/02', 'YYYY/MM/DD') to convert your date values to the standard database date format

Replace the above date value as your date columns

user_11865809 · Answer

using to_date makes the script robust and independent from an implicit format. It also makes the code more readable since to_date is quite obvious in its syntax.

ORA-01843: not a valid month when inserting a date

3 Answers