SQL "not a valid month"

Question

I have a table in sql as follows:

CREATE TABLE Reserves(
    sid INTEGER,
    bid INTEGER,
    day DATE,
    PRIMARY KEY (sid, bid, day),
    FOREIGN KEY (sid) REFERENCES Sailors,
    FOREIGN KEY (bid) REFERENCES Boats
);

and I'm trying to insert into it:

INSERT INTO Reserves VALUES(22, 101, '01-01-1998');

But I get the error: ORA-01843: not a valid month

This is an Oracle db. I'm not sure what's wrong with my date format.

Answer 1

As @Jody also mentioned,
You can change the default for your session by executing this code once before INSERT :

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

You may alter the format in any order you like.

Source: dba-oracle.com

Answer 2

It's not entirely clear which you wanted, so you could try:

• For month-day-year format:

  INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','MM-DD-YYYY'));

• For day-month-year format:

  INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','DD-MM-YYYY'));

Also, recommended reading: Oracle functions: TO_DATE

Answer 3

You can use the date keyword to specify an ANSI-standard date string:

INSERT INTO Reserves VALUES(22, 101, date '1998-01-01');

In this case, the format is YYYY-MM-DD, or January 1, 1998.