Date Format

The date columns in SQL* Plus are returned in the format 'DD-MON-YY', in order to obtain the time along with the date - one uses to_char() function with the date datatype column and the date format as arguments to display the date along with timestamp in what ever format one desires.

Say a query against a table which has only date columns then it is highly frustrating to use the to_char function on every data datatype column to change the format to include the timestamp. This is where the NLS_DATE_FORMAT parameter comes into picture. Set the NLS_DATE_FORMAT to what ever format the date has to be displayed, all the date datatypes columns in a query return the output in the same format specified with NLS_DATE_FORMAT. Look at the examples below.

SQL> select last_date,next_date from dba_jobs;

LAST_DATE NEXT_DATE
--------- ---------
24-MAY-09 25-MAY-09
25-MAY-09 25-MAY-09
09-FEB-09 01-JAN-00
01-MAR-09 01-JAN-00
24-MAY-09 25-MAY-09
25-MAY-09 25-MAY-09
25-MAY-09 26-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
25-MAY-09 26-MAY-09
25-MAY-09 26-MAY-09
25-MAY-09 26-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09

16 rows selected.

SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss';

Session altered.

SQL> select last_date,next_date from dba_jobs;

LAST_DATE           NEXT_DATE
------------------- -------------------
05-24-2009 09:56:49 05-25-2009 08:56:49
05-25-2009 05:28:03 05-25-2009 05:34:03
02-09-2009 16:48:24 01-01-4000 00:00:00
03-01-2009 23:19:11 01-01-4000 00:00:00
05-24-2009 16:18:10 05-25-2009 15:18:10
05-25-2009 00:02:09 05-25-2009 23:00:00
05-25-2009 01:02:12 05-26-2009 00:00:00
05-24-2009 18:28:18 05-25-2009 17:28:18
05-24-2009 21:03:09 05-25-2009 09:03:09
05-25-2009 00:00:03 05-26-2009 00:00:00
05-25-2009 00:00:13 05-26-2009 00:00:04
05-25-2009 00:00:14 05-26-2009 00:00:04
05-24-2009 18:28:13 05-25-2009 17:28:13
05-24-2009 16:15:01 05-25-2009 15:15:01
05-24-2009 16:15:00 05-25-2009 15:15:00
05-24-2009 16:14:59 05-25-2009 15:14:59

16 rows selected.