Error when trying to insert values into a table and not sure why

Question

I am using Oracle and I am trying to insert values into a table called bug but i am not sure what it means and how to fix it.

My error can be seen below:

Error starting at line : 78 in command - INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (00, 'Crash', 'Software stopped functioning properly and exited', DATE '1980-03-20 09:26:50')

Error at Command Line : 78 Column : 133 Error report - SQL Error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string"

*Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.

*Action: Correct the format string to match the literal.

Any help will be greatly appreciated.

My code so far can be seen below:

--CREATE SCRIPTS
/*put your create scripts here – your script should not commented out*/

CREATE TABLE Project
(
    Proj_ID integer,
    Proj_Name varchar(10),
    Proj_Start_Date date,
    primary key (Proj_ID)
);

CREATE TABLE Bug
(
    Bug_ID integer,
    Bug_Type varchar(40),
    Bug_Desc varchar(20),
    Bug_Time date,
    primary key(Bug_ID)
);

CREATE TABLE Engineer
(
    Engineer_ID integer,
    Engineer_Name varchar(10),
    Engineer_Type varchar(20),
    primary key (Engineer_ID)
);

CREATE TABLE Bug_Project
(
    Bug_ID integer,
    Proj_ID integer,
    primary key(Bug_ID, Proj_ID),
    foreign key(Bug_ID) references Bug (Bug_ID),
    foreign key(Proj_ID) references  Project (Proj_ID)
);

CREATE TABLE Fix_Allocation
(
    Engineer_ID integer,
    Bug_ID integer,
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

CREATE TABLE Test_Allocation
(
    Engineer_ID integer,
    Bug_ID integer,
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

CREATE TABLE Note
(
    Engineer_ID integer,
    Bug_ID integer,
    Note_author varchar(10),
    Note_contents varchar(20),
    primary key(Engineer_ID, Bug_ID),
    foreign key(Engineer_ID) references Engineer (Engineer_ID),
    foreign key(Bug_ID) references Bug (Bug_ID)
);

COMMIT;
--INSERT SCRIPTS
/*put your insert scripts here – your script should not commented out */

INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (00, 'Project 1', DATE '1980-02-14');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (01, 'Project 2', DATE '1985-12-11');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (02, 'Project 3', DATE '1992-06-03');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (03, 'Project 4', DATE '2000-07-22');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (04, 'Project 5', DATE '2012-03-19');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (05, 'Project 6', DATE '2015-10-21');

INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (00, 'Crash', 'Software stopped functioning properly and exited', DATE '1980-03-20 09:26:50');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logical error', DATE '1982-06-31 11:36:32');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (02, 'Compilation Error', 'Problems with the compiler, failed complication of source code', DATE '1987-07-12 14:11:15');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (03, 'Crash', 'Software stopped functioning properly and exited', DATE '1993-01-31 03:21:17');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (04, 'Logical Error', 'Unexpected behavior due to problem in source code', DATE '1997-04-01 10:46:18');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (05, 'Run Time Error', 'Wrong output due to a logical error', DATE '2001-12-24 12:12:37');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (06, 'GUI Error', 'Glitchy interface ', DATE '2005-09-02 17:11:55');

INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (00, 'Ava', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (01, 'Alexander', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (02, 'Aiden', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (03, 'Anthony', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (04, 'Adam', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (05, 'Alex', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (06, 'John', 'Fixer');

INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();

INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();

INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();

INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();

COMMIT;
--SELECT SCRIPT
/*put your select scripts here (with indication of which query is answered) – your script should not commented out

-- Query 1:  List of all the bugs, and their details.
SELECT * FROM Bug;

-- Query 2: List of all bugs, and their notes.

-- Query 3: List of all bugs, with their notes, and the engineers who have written them; sorted by name of engineer.

-- Query 4: List the bugs and how much cumulative time (in hours) they have taken; ordered by time taken.

-- Query 5: The bug that has taken most time to fix, and the projects it is connected to.

COMMIT;
--DROP SCRIPT
/*put your drop scripts here (in the correct order)– your script should not commented out

DROP TABLE Note;
DROP TABLE Test_Allocation;
DROP TABLE Fix_Allocation;
DROP TABLE Engineer;
DROP TABLE Bug_Project;
DROP TABLE Bug;
DROP TABLE Project;

COMMIT;

EDIT:

INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logical error', To_DATE ('1982-06-31 11:36:32', 'YYYY-MM-DD HH24:MO:SS'));

This is the error message i got:

Error starting at line : 79 in command - INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logical error', To_DATE ('1982-06-31 11:36:32', 'YYYY-MM-DD HH24:MO:SS')) Error report - ORA-01821: date format not recognized

the format mask for minutes is MI, so your to_date() should have the format mask of yyyy-mm-dd-hh24:mi:ss (caps or lower case, it doesn't matter). However, you have another problem: the 31st June doesn't exist (June only has 30 days). — Boneist

Popeye Popeye · Accepted Answer · 2019-12-03T11:26:28

Date function can only take 'YYYY-MM-DD' format.

DATE '1982-06-31 11:36:32' is wrong.

You can use to_date for it as following.

To_DATE ('1982-06-31 11:36:32,'YYYY-MM-DD HH24:MI:SS') 
-- hour in 24 hour format as can be seen in other dates in your example.
-- if your date contains AM or PM then you can use, To_DATE ('1982-06-31 11:36:32 AM' ,'YYYY-MM-DD HH:MI:SS AM')

Cheers!!

Error when trying to insert values into a table and not sure why

2 Answers