Display Date Type in Oracle SQLPLUS

To display the date type in the desired format, we can manually format the date using to_char() function. Or, we use "alter session set NLS_DATE_FORMAT" to set the environment variable and the format will be applied to all date columns in any queries.

SQL> select sysdate from dual;

SYSDATE
---------
16-MAR-15

SQL> select to_char(sysdate,'YYYYMMDD:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'
-----------------
20150316:07:16:02

SQL> alter session set NLS_DATE_FORMAT = 'YYYYMMDD:HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------
20150316:07:16:31