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.

As a general rule you should always use a culture inspecific date format (yyyyMMdd) unless you are declaring the date format explcitly. 02-01-2012 could be 2nd January 2012 or 1st February 2012, however 20120102 is always 2nd January 2012 — GarethD
– GarethD, Commented Oct 21, 2012 at 22:01

ppeterka · Accepted Answer · 2013-03-07 11:02:37Z

27

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

edited Mar 7, 2013 at 11:02

answered Oct 21, 2012 at 21:59

ppeterka

20.8k6 gold badges67 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BellevueBob · Accepted Answer · 2012-10-21 22:21:50Z

12

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.

answered Oct 21, 2012 at 22:21

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

1 Comment

Matt Over a year ago

Thanks Bob. I was hoping there was a way that I wouldn't have to use the TO_DATE function.

Hamed · Accepted Answer · 2014-07-09 14:22:59Z

8

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

edited Jul 9, 2014 at 14:22

answered Mar 1, 2014 at 21:10

Hamed

86910 silver badges26 bronze badges

Comments

Jody · Accepted Answer · 2012-10-21 22:02:31Z

1

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

answered Oct 21, 2012 at 22:02

Jody

8,3614 gold badges29 silver badges29 bronze badges

Comments

Shabbir Ali · Accepted Answer · 2019-07-19 12:12:11Z

1

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.

answered Jul 19, 2019 at 12:12

Shabbir Ali

1741 silver badge11 bronze badges

Comments

Suraj Rao · Accepted Answer · 2022-05-21 09:24:39Z

0

Try this it will solve your issue

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

edited May 21, 2022 at 9:24

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered May 21, 2022 at 9:21

Ashish Singh

1

1 Comment

Chris Catignani Over a year ago

How is this different than the accepted answer?

Collectives™ on Stack Overflow

SQL "not a valid month"

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related