CODEWITHSUNDEEP
sqloracle-database
Matt
Matt

Reputation: 5778

SQL "not a valid month"

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.

Upvotes: 10

Views: 111095

Answers (6)

Ashish Singh
Ashish Singh

Reputation: 1

Try this it will solve your issue

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

Upvotes: 0

Shabbir Ali
Shabbir Ali

Reputation: 174

Also you can try in below query format:

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

DATE keyword interprets the following string as a date.

Upvotes: 1

Hamed
Hamed

Reputation: 869

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

Upvotes: 8

ppeterka
ppeterka

Reputation: 20726

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

Upvotes: 27

BellevueBob
BellevueBob

Reputation: 9618

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.

Upvotes: 12

Jody
Jody

Reputation: 8291

Try '1998-01-01'.

I believe the default date format for oracle is yyyy-mm-dd. You can change the default for your session by using alter session set nls_date_format='mm-dd-yyyy'

Keep in mind that most clients let you set this to whatever you like permanently

Upvotes: 1

Related Questions