Date Formats

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
select to_char(sysdate, 'YYYY/MM/DD') from dual;
select to_char(sysdate, 'MON') from dual;
select to_char(sysdate, 'MONTH') from dual;
select to_char(sysdate, 'DDD') from dual;
select to_char(sysdate, 'DD') from dual;
select to_char(sysdate, 'D') from dual;
select to_char(sysdate, 'DAY') from dual;
select to_char(sysdate, 'HH:MI:SS') from dual;
select to_char(sysdate, 'HH24:MI:SS') from dual;

Format	Description
YY	A 2 digit year such as 87
YYYY	A 4 digit year such as 1987
MON	The month abbreviation such as JUL
MONTH	The full name of the month such as JULY
DDD	The Julian day of the year such as 196 for July 15th

Examples

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
15-JUL-2002 07:31:18


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

SQL> select to_char(sysdate, 'MON') from dual;
---
JUL

SQL> select to_char(sysdate, 'DDD') from dual;
---
196

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

SQL> select to_char(sysdate, 'MON') from dual;
---
JUL

SQL> select to_char(sysdate, 'MONTH') from dual;
---------
JULY

SQL> select to_char(sysdate, 'DDD') from dual;
---
196

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

SQL> select to_char(sysdate, 'D') from dual;
-
2

SQL> select to_char(sysdate, 'DAY') from dual;
---------
MONDAY

SQL> select to_char(sysdate, 'HH:MI:SS') from dual;
--------
07:21:08

SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;
--------
19:21:11