I am getting error literal does not match format string

Question

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

You are supplying a date and time to columns that are typed as `DATE`. Perhaps you wanted `TIMESTAMP` instead? — Tim Roberts, Jul 29 '22 at 18:57
You're tagged MySQL, but you're getting an ORA (Oracle) error. Which database are you using? — Schwern, Jul 29 '22 at 19:06
Does this answer your question? [SQL Error: ORA-01861: literal does not match format string 01861](https://stackoverflow.com/questions/22542882/sql-error-ora-01861-literal-does-not-match-format-string-01861) — Schwern, Jul 29 '22 at 19:06

score 1 · Answer 1 · answered Jul 29 '22 at 20:55

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');

score 0 · Answer 2 · answered Jul 29 '22 at 19:16

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.

I am getting error literal does not match format string

2 Answers2