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.
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.
No comments:
Post a Comment