I am getting error literal does not match format string-CodePudding

I am trying to insert data into an existing table and keep receiving an error. The table script is bellow;

CREATE TABLE FLIGHT (
    TicketID INT NOT NULL,
    DipartureDate DATE,
    ArrivalDate DATE,
    TicketType VARCHAR(255),
    TicketPrice INT,
    
    AirplaneID INT NOT NULL,
    DepartureAirport VARCHAR(255) NOT NULL,
    ArrivalAirport VARCHAR(255) NOT NULL,
    Airlinecompany VARCHAR(255) NOT NULL,
    
    PRIMARY KEY (TicketID),

    FOREIGN KEY (AirplaneID) REFERENCES AIRPLANE(AirplaneID),
    FOREIGN KEY (DepartureAirport) REFERENCES AIRPORT(AirportName),
    FOREIGN KEY (ArrivalAirport) REFERENCES AIRPORT(AirportName),
    FOREIGN KEY (Airlinecompany) REFERENCES AIRLINE(Company)
);
``
The data I want to insert is bellow;

``
INSERT INTO FLIGHT
VALUES (12, '2021-09-21 13:50:00', '2021-09-21 20:00:00', 'Bis', 800,
233, 'HA', 'CC', 'Azal');

INSERT INTO FLIGHT
VALUES (45, '2021-06-11 10:00:00', '2021-06-11 14:30:00', 'Ec', 200,
551, 'IST', 'TA', 'Turkish');

INSERT INTO FLIGHT
VALUES (67, '2021-08-08 05:30:00', '2021-08-08 15:30:00', 'Bis', 900,
889, 'ND', 'GE', 'Geo');

INSERT INTO FLIGHT
VALUES (72, '2021-10-04 14:00:00', '2021-10-05 10:00:00', 'Bis', 1100,
481, 'CAN', 'DOM', 'Aeroflot');

INSERT INTO FLIGHT
VALUES (55, '2021-05-20 02:30:00', '2021-05-20 08:30:00', 'Ec', 400,
766, 'BE', 'KC', 'Pekin');

INSERT INTO FLIGHT
VALUES (96, '2021-05-22 04:00:00', '2021-05-22 14:00:00', 'Ec', 350,
122, 'ND', 'HA', 'Delhi');

INSERT INTO FLIGHT
VALUES (03, '2021-11-17 11:00:00', '2021-11-17 20:00:00', 'Ec', 500,
663, 'CC', 'DOM', 'Aeroflot');

INSERT INTO FLIGHT
VALUES (88, '2021-12-12 02:40:00', '2021-12-12 05:40:00', 'Bis', 700,
334, 'IST', 'KC', 'Ukr');

INSERT INTO FLIGHT
VALUES (19, '2021-07-07 00:20:00', '2021-07-07 04:20:00', 'Ec', 250,
887, 'TA', 'GE', 'IS');

INSERT INTO FLIGHT
VALUES (31, '2021-11-13 10:15:00', '2021-11-13 17:15:00', 'Bis', 600,
111, 'BE', 'CC', 'Masr');

I am getting error which is bellow

INSERT INTO FLIGHT
VALUES (12, '2021-09-21 ', '2021-09-21 ', 'Bis', 800,
233, 'HA', 'CC', 'Azal')
Error report -
ORA-01861: literal does not match format string

CodePudding user response：

DipartureDate and ArrivalDate are date types. These accept the date as "day-month-year" not "year-month-day".

You need to tell Oracle how to parse the date. Use to_date. Try TO_DATE('2021-09-21 13:50:00','YYYY-MM-DD hh24:mi:ss'). Or change the literals by hand to match Oracle's expected format.

CodePudding user response：

You have datetime columns

DipartureDate DATE,
ArrivalDate DATE,

(DATE, despite its name, is not a date, but a datetime in Oracle, as you already know).

You want to fill the column with values like '2021-09-21 13:50:00'. This, however, is a string. Oracle will try to convert this string into a datetime (DATE), but whether it succeeds or not depends on session settings. Don't do this.

Use a datetime literal instead, which is your string in exactly the format you are already using preceded by the key word TIMESTAMP.

INSERT INTO flight
VALUES (12, TIMESTAMP '2021-09-21 13:50:00', TIMESTAMP '2021-09-21 20:00:00',
        'Bis', 800, 233, 'HA', 'CC', 'Azal');