SQL> select ename, TO_CHAR(hiredate, 'dd-mm-yyyy') from emp
2

* enter key 2 times:

* Then save

SQL> save dateformat
Created file dateformat.sql
SQL> edit dateformat

SQL>
SQL>
SQL> start dateformat

ENAME TO_CHAR(HI
---------- ----------
SMITH 17-12-1980
ALLEN 20-02-1981
WARD 22-02-1981
JONES 02-04-1981
MARTIN 28-09-1981
BLAKE 01-05-1981
CLARK 09-06-1981
SCOTT 19-04-1987
KING 17-11-1981
TURNER 08-09-1981
ADAMS 23-05-1987
JAMES 03-12-1981
FORD 03-12-1981
MILLER 23-01-1982

 

GET THE SYSTEM DATE

SQL> select SYSDATE from dual;

SYSDATE
---------
19-DEC-06
SQL>

 

MORE EXAMPLE ON DATE FORMAT:

SQL> edit dateformat

select 'dd-mon-yyyy -> ' || TO_CHAR(SYSDATE, 'dd-mon-yyyy') AS "Title Header 1" from dual;
select 'dd-Mon-yyyy -> ' || TO_CHAR(SYSDATE, 'dd-Mon-yyyy') AS "Title Header 2" from dual;
select 'DD-MON-YY -> ' || TO_CHAR(SYSDATE, 'DD-MON-YY') AS "Title Header 3" from dual;
select 'DDspth-MONTH-YEAR -> ' || TO_CHAR(SYSDATE, 'DDspth-MONTH-YEAR') AS "Title Header 4" from dual;
-- =============================================


select ename, TO_CHAR(hiredate, 'dd-mm-yyyy') from emp
/

SQL> start dateformat

Title Header 1
--------------------------
dd-mon-yyyy -> 19-dec-2006

Title Header 2
--------------------------
dd-Mon-yyyy -> 19-Dec-2006

Title Header 3
----------------------
DD-MON-YY -> 19-DEC-06

Title Header 4
------------------------------------------------------------
DDspth-MONTH-YEAR -> NINETEENTH-DECEMBER -TWO THOUSAND SIX

ENAME TO_CHAR(HI
---------- ----------
SMITH 17-12-1980
ALLEN 20-02-1981
WARD 22-02-1981
JONES 02-04-1981
MARTIN 28-09-1981
BLAKE 01-05-1981
CLARK 09-06-1981
SCOTT 19-04-1987
KING 17-11-1981
TURNER 08-09-1981
ADAMS 23-05-1987
JAMES 03-12-1981
FORD 03-12-1981
MILLER 23-01-1982
SQL>

 

MORE DATE FORMAT EXAMPLE:

-- ===========================

select ( 'dd-mon-yyyy -> '||TO_CHAR(SYSDATE, 'dd-mon-yyyy') ) AS " " from dual;

-- ===========================

select ('dd-Mon-yyyy -> '||TO_CHAR(SYSDATE, 'dd-Mon-yyyy')) AS " " from dual;

-- ===========================

select ('DD-MON-YYYY -> '||TO_CHAR(SYSDATE, 'DD-MON-YYYY')) AS " " from dual;

-- ===========================

select ('Month dd, yyyy -> '||TO_CHAR(SYSDATE, 'Month dd, yyyy')) AS " " from dual;

-- ===========================

select ('mm/dd/yyyy -> '||TO_CHAR(SYSDATE, 'mm/dd/yyyy')) AS " " from dual;

-- ====================

select ('Day -> '||TO_CHAR(SYSDATE, 'Day')) AS " " from dual;

-- ===========================

select (ename||' joined on '||TO_CHAR(hiredate, '"the "ddspth" day of "Month, YYYY')) AS " " from emp;

-- ===========================

/