The date format of the oracle database normally defaults to DD-MON-YYYY in American Language standards. However, there are times when you want to see the hours, minutes and even seconds in which a transaction occurred. There are to ways of performing this task in the Oracle database: convert the date field to a char with the to_char function, specifying the desired format or changing the default NLS_DATE_FORMAT of the system or session. In this procedure we will demonstrate changing the NLS_DATE_FORMAT of the session.
1. Logon to you oracle database as an administrator.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Wed Apr 14 10:18:48 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Perform a select on the V$INSTANCE view for startup_time to see the current default date format of your NLS_DATE_FORMAT parameter.
SQL> select startup_time from v$instance;
STARTUP_T
———
14-APR-10
SQL>
3. NOTE: The current format only shows the day-month-year of the instance startup.
4. Perform a select on the view NLS_SESSION_PARAMETERS and you can see that the format of NLS_DATE_FORMAT is DD-MON-RR.
SQL> select * from nls_session_parameters;
PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL>
5. Perform an alter session statement to change the default format to include hours, minutes, and seconds.
alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
6. Again perform a select on the view NLS_SESSION_PARAMETERS.
SQL> select * from nls_session_parameters;
PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL>
7. Now select the startup_time from the view V$INSTANCE and you will see the hours, minutes, and seconds of the instance startup. All date variables will be displayed in this format for the rest of the current session.
SQL> select startup_time from v$instance;
STARTUP_TIME
——————–
14-APR-2010 07:27:18
SQL>
NOTE: Changing this parameter at the system level will change the display of dates for all sessions in the database.
This completes changing the default date format in an Oracle database.
Larry J Catt, OCP 9i. 10g
oracle@allcompute.com
www.allcompute.com