Display Date in SQLPLUS Client

When I select column of date type within SQLPLUS, I got the following result.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
---------
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
03-OCT-11
23-OCT-11
23-OCT-11
23-OCT-11

9 rows selected.

Since I want to see the hour, minute and second of a transaction, I use to_char() to format the date.

SQL> select to_char(txn_date,'YYYYMMDD:hh24:mi:ss') from card_txn 
where rownum <10;

TO_CHAR(TXN_DATE,
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.

If we want to display dates in desired format without using to_char() conversion, we can use the following command. After running this command, all dates will be automatically formatted.

SQL> alter session set nls_date_format='YYYYMMDD:hh24:mi:ss';

Session altered.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.