SQL> select value from V$nls_parameters where parameter='NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------
DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
---------
06-JAN-04

SQL> alter session set nls_date_format='mm-dd-yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
01-06-2004

SQL> alter session set nls_date_format='DD/MM/YY HH:MI';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------
06/01/04 10:18

SQL> select to_char(sysdate,'MM-DD-YYYY') from dual;

TO_CHAR(SY
----------
01-06-2004

SQL> create table t(dt date);

Table created.

SQL> insert into t values('10-DEC-99');

1 row created.

-- 10-DEC-99 character string is compatible with my session date format DD-MON-RR

-- using to_date to interpret the string 28/SEP/03

SQL> insert into t values(to_date('28/SEP/03','DD/MON/YY'));

1 row created.

SQL> insert into t values('050505');
insert into t values('050505')
                     *
ERROR at line 1:
ORA-01861: literal does not match format string

-- 050505 fails becos its incompatible with DD-MON-RR

SQL> insert into t values(to_date('050505','DDMMYY'));

1 row created.

-- so used to_date to inform Oracle of the date format

-- dates are displayed in the same session date format,irrespective of the format you used for inserting

SQL> select * from t;

DT
---------
10-DEC-99
28-SEP-03
05-MAY-05

-- you can change the display format to anything you want

SQL> select to_char(dt,'YYYY-MM-DD') from t;

TO_CHAR(DT
----------
1999-12-10
2003-09-28
2005-05-05

CREATE TABLE prjct
  (prjct_id      NUMBER   (  10) NOT NULL,
   prjct_name    VARCHAR2 (  50) NOT NULL,
   prjct_desc    VARCHAR2 (2000) NULL ,
   strt_date     DATE            NULL
   CONSTRAINT    ckc_strt_date_prjct 
   CHECK         (strt_date IS NULL 
                  OR (strt_date BETWEEN <b>TO_DATE ('01/01/1970', 'DD/MM/YYYY')</b> 
                                AND     <b>TO_DATE ('31/1/2050', 'DD/MM/YYYY')</b>)),
   end_date      DATE NULL
   CONSTRAINT    ckc_end_date_prjct 
   CHECK         (end_date is NULL 
                  OR (end_date BETWEEN <b>TO_DATE ('01/01/1970', 'DD/MM/YYYY')</b> 
                               AND     <b>TO_DATE ('31/12/2050', 'DD/MM/YYYY')</b>)),
   prdctn        VARCHAR2 (   1) NULL
   CONSTRAINT    ckc_prdctn_prjct 
   CHECK         (prdctn is NULL 
                  OR (prdctn IN ('Y','N'))),
   prjct_ownr_id NUMBER   (   5) NOT NULL,
   stream        VARCHAR2 (  25) NOT NULL,
   CONSTRAINT    pk_prjct 
   PRIMARY KEY   (PRJCT_ID)
   USING INDEX TABLESPACE prs_idx)
  TABLESPACE psrs_tabs
/