Here is the table
SQL> SQL> drop table emp; SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL>-- ww w. j a v a 2 s. co m SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL>
SQL> SQL> drop table emp; Table dropped.-- www . j ava 2 s . c om SQL> create table emp( 2 empno number(4,0), 3 ename varchar2(10), 4 job varchar2(9), 5 mgr number(4,0), 6 hiredate date, 7 sal number(7,2), 8 comm number(7,2), 9 deptno number(2,0) 10 ); Table created. SQL> SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10); SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30); SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10); SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20); SQL> insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20); SQL> insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20); SQL> insert into emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20); SQL> insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30); SQL> insert into emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30); SQL> insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30); SQL> insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30); SQL> insert into emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20); SQL> insert into emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30); SQL> insert into emp values(7934, 'MILLER', 'CLERK', 7782,to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10); SQL> SQL> SQL> select ename 2 , to_char(hiredate,'fmMonth ddth, yyyy') 3 from emp; ENAME TO_CHAR(HIREDATE,'FMMONTHDDTH,YYYY') ---------- ----------------------------------------------- KING November 17th, 1981 BLAKE May 1st, 1981 CLARK June 9th, 1981 JONES April 2nd, 1981 SCOTT April 19th, 1987 FORD December 3rd, 1981 SMITH December 17th, 1980 ALLEN February 20th, 1981 WARD February 22nd, 1981 MARTIN September 28th, 1981 TURNER September 8th, 1981 ENAME TO_CHAR(HIREDATE,'FMMONTHDDTH,YYYY') ---------- ----------------------------------------------- ADAMS May 23rd, 1987 JAMES December 3rd, 1981 MILLER January 23rd, 1982 14 rows selected. SQL>