How to insert date in Oracle 10g xe?

Question

I did the following:

1.   CREATE TABLE BOOK(
             BOOK_ID VARCHAR(4) PRIMARY KEY,
             ISBN_10 VARCHAR(10),
             TITLE VARCHAR(50),
             CATEGORY VARCHAR(25),
             PRICE DECIMAL(6,2),
             BINDING VARCHAR(2),
             PUB_DATE DATE,
             AUTHOR_ID SMALLINT,
             PUBLISHER_ID SMALLINT
             );
   

2.

ALTER SESSION SET nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

3.

INSERT INTO  BOOK
VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S',**'26-01-1991'**,13,103);

It gave an error: ORA-01843: not a valid month

However if do the following there is no problem:

Query:

INSERT INTO 
BOOK
VALUES('4','123459','INTRODUCTION TO Small Talk','IT',157.00,'S','**26-JAN-1991**',13,103);

Can anyone explain why?

Answer 1

You want to read the Datetime Literals section of the manual. Alternatives are:

• Use a date literal: DATE '1991-01-26'
• Convert from string: TO_DATE('26-01-1991', 'DD-MM-YYYY')

If you set NLS_DATE_FORMAT you can omit TO_DATE()'s second argument but not skip the function entirely.

See also: Datetime Format Models