Oracle nls_date_format tips
Question: In understand that the internal
storage of a DATE or TIMESTAMP column is independent of the display
value. Can you please explain how nls_date_format is
used to display date values?
Answer: The Oracle nls_date_format environmental variable does
much more than control the date display within Oracle.
Changing the
value of nls_date_format will change the way that the data is
displayed in Oracle SQL*Plus:
ALTER SESSION SET
NLS_DATE_FORMAT = 'YYYY MM DD';
select sysdate from dual;
ALTER SESSION SET
NLS_DATE_FORMAT = 'HH24:MI:SS';
select sysdate from dual;
You can change the way that a date or timestamp column is display
at any time by altering your session to re-set nls_date_format.
You can use this alter session set nls_date_format in
SQL*Plus, PL/SQL:
alter session set nls_date_format = 'YYYY MM
DD';
alter session set nls_date_format = 'HH24:MI:SS';
You can also change the nls_date_format in many procedural
languages such as C, C++ COBOL, Java, JavaScript, PHP and many
others. In any language with an Oracle pre-compiler (Pro*C,
Pro*COBOL, Pro*Fortran, etc.), you can enter the alter session
set nls_date_format directly within your program.
Oracle will accept the command and comment out the Oracle
directives before passing the program to the native language
complier. In other languages that don;t support direct "alter
session set nls_date_format", there is separate syntax::
- Oracle Pro*C:
exec sql
alter session
- Perl DBI:
$db=DBI->connect("dbi:Oracle:Local","scott","tiger");
$db->do("ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'");
You can also use the use the to_char format mask to change the
display value of a date or timestamp column:
select to_char(sysdate,'HH24:MI:SS')
from dual;
select
to_char(sysdate,'YYY MM DD') from dual;
The nls_date_format also allows you to display a date up to 1/100
of a second, quite precise date display:
alter session set nls_date_format =
'DD-Mon-YYYY HH24:MI:SS.FF'
Beware, the system wide default for nls_date_format requires a
database bounce and you cannot change nls_date_format system wide
with an alter system set nls_date_format
statement:
SQL>
alter system set nls_date_format='Month DD, yyyy';
alter system
set nls_date_format='Month DD, yyyy'
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with
this
option
Also see how to
change your
nls_date_format at login time.
For more details of using nls_date format to change the data
display format, see our related notes on
using nls_date_format in specific conditions.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
