ORA-00936: missing expression on inserting a row-CodePudding

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

CodePudding user response：

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

CodePudding user response：

As you already see, Oracle doesn't have a date or a time data type. They only have a datetime that they inappropriately call DATE.

If we want to store dates, the time part is just set to 00:00:00 (midnight). If we want to store a date with a time, we store the combination. In some rare cases do we separate date and time in two columns. This is the case when one of them can be empty (null). Thus we can distinguish midnight from "no time applies" and we can store "every day at 16:15". But, as mentioned, this is a rare case.

In your case all columns are NOT NULL columns. So, there is no reason to separate the values. The separation even makes some queries more complicated than necessary, and they become more error-prone thus.

So, change your table design:

CREATE TABLE booking
(
  booking_id          VARCHAR2(4)    NOT NULL,
  booking_time        DATE           NOT NULL,
  checkin_time        DATE           NOT NULL,
  checkout_time       DATE           NOT NULL,
  number_of_adults    INTEGER        NOT NULL,
  number_of_children  INTEGER        NOT NULL,
  specialrequest      VARCHAR2(100),
  PRIMARY KEY (booking_id)
);

Then use datetime / timestamp literals to fill the table:

INSERT INTO booking
(
  booking_id,
  booking_time,
  checkin_time,
  checkout_time,
  number_of_adults,
  number_of_children,
  specialrequest
)
VALUES
(
  4011,
  TIMESTAMP '2022-05-01 01:00:00',
  TIMESTAMP '2022-07-01 14:00:00',
  TIMESTAMP '2022-07-03 15:00:00',
  2,
  0,
  'Birthday Cake for dinner on July 2, 2022'
);