SQL Oracle Date issue: ORA-01722: invalid number

Question

Wanted to have the date format as DD/MM/YYYY

I used:

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

Now when I write the line:

INSERT INTO staff Values ('ST01', 'Mrs', 'Katie', 'Elswood', '06/10/1990');

I get the error:

ERROR at line 1: ORA-01722: invalid number

Any ideas whats wrong? thanks.

or i get the error message not a valid month?

STAFF_ID                                  NOT NULL VARCHAR2(6)
TITLE                                              VARCHAR2(20)
FIRST_NAME                                         VARCHAR2(20)
SURNAME                                            VARCHAR2(20)
DOB                                                DATE
ADDRESS                                            VARCHAR2(50)
HOME_NO                                            VARCHAR2(20)
MOBILE                                             VARCHAR2(20)
EMAIL                                              VARCHAR2(30)
NI                                                 VARCHAR2(9)
SALARY                                             NUMBER(13,4)
EMPLOMENT_DATE                                     DATE
BRANCH_ID                                          VARCHAR2(3)
POSTCODE                                           VARCHAR2(8)
POSITION                                           VARCHAR2(30)
TOWN                                               VARCHAR2(30)

Answer 1

This is never a date issue. While inserting if you are not inserting all the values then oracle would throw out an error message. Hence please explicilty mention the column names for which you are going to insert the values.

Insert into staff(staff_id,title,first_name,surname,dob) values ('ST01','Mrs','Katie','Elswood','06/10/1990');