ORA-00936: missing expression on inserting a row

July 25, 2022

I was initially using TO_DATE instead of DATE but i had to take it off because it said invalid. Now i have another mistake "ORA-00936: missing expression". My table is successfully created but i can’t insert a row. Here’s my code:

CREATE TABLE BOOKING(
  BOOKINGID varchar(4) NOT NULL,
  BOOKINGDATE date NOT NULL,
  BOOKINGTIME date NOT NULL,
  CHECKINDATE date NOT NULL,
  CHECKIN date NOT NULL,
  CHECKOUTDATE date NOT NULL,
  CHECKOUT date NOT NULL,
  NUMOFADULTS integer NOT NULL,
  NUMOFCHILDREN integer NOT NULL,
  SPECIALREQUEST varchar(100)
);

INSERT INTO BOOKING VALUES(
  4011, 
  DATE('01/05/2022', 'DD/MM/YYYY'), DATE('1:00PM', 'H:MI AM'),
  DATE('01-07-2022', 'DD/MM/YYYY'), DATE('2:00PM', 'H:MI AM'),
  DATE('03-07-2022', 'DD/MM/YYYY'), DATE('3:00PM', 'H:MI AM'),
  2, 0, 'Birthday Cake for dinner on 09-07-2022'
);

>Solution :

You should be using TO_DATE(), not DATE(). But your format strings are not correct. You need HH for the hours, not H. And when the date has - separators, you need that in the format string, not /.

INSERT INTO BOOKING VALUES(
    4011, 
    TO_DATE('01/05/2022', 'DD/MM/YYYY'), 
    TO_DATE('1:00PM', 'HH:MI AM'), 
    TO_DATE('01-07-2022', 'DD-MM-YYYY'), 
    TO_DATE('2:00PM', 'HH:MI AM'), 
    TO_DATE('03-07-2022', 'DD-MM-YYYY'), 
    TO_DATE('3:00PM', 'H:MI AM'), 
    2, 0, 'Birthday Cake for dinner on 09-07-2022');