Using the NLS-DATE-LANGUAGE Parameter
Establishing a Globalization Support Environment
Working in a Global Environment 6-13
1.
In SQL Developer, make a note of the current territory and date format in which
Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database,
and then National Language Support Parameters. In the Select Connection dialog
box, select
hr_conn
from the list of connections. The current date format is listed
after
NLS2008FORMAT
and the current territory is listed after
NLS_TERRITORY
.
2.
Set
NLS_TERRITORY
to America.
ALTER SESSION SET NLS_TERRITORY = America;
3.
Select the dates using the format models.
SELECT hire_date, TO_CHAR(hire_date,'DS') "Short",
TO_CHAR(hire_date,'DL') "Long" FROM employees
WHERE employee_id IN (111, 112, 113);
The output from the example should be similar to the following.
HIRE_DATE Short Long
---------------- -------- -----------------------------------
30-SEP-97 9/30/1997 Tuesday, September 30, 1997
07-MAR-98 3/7/1998 Saturday, March 07, 1998
07-DEC-99 12/7/1999 Tuesday, December 07, 1999
4.
Set
NLS_TERRITORY
and
NLS2008FORMAT
back to their original settings listed
ALTER SESSION SET NLS_TERRITORY=AMERICA;
ALTER SESSION SET NLS2008FORMAT="MM/DD/YYYY";
To use the NLS2008LANGUAGE parameter:
The
NLS2008LANGUAGE
parameter specifies the language for the day and month
produced by the
TO_CHAR
and
TO_DATE
functions.
NLS2008LANGUAGE
overrides
the language that is specified implicitly by
NLS_LANGUAGE
. The
NLS2008
LANGUAGE
parameter has the same syntax as the
NLS_LANGUAGE
parameter, and all
supported languages are valid values.
The
NLS2008LANGUAGE
parameter also determines the language used for:
Month and day abbreviations returned by the
TO_CHAR
and
TO_DATE
functions
Month and day abbreviations used by the default date format (
NLS2008
FORMAT
)
The default date format uses the month abbreviations determined by the
NLS2008
LANGUAGE
parameter. For example, if the default date format is
DD-MON-YYYY
and
NLS2008LANGUAGE
=
FRENCH
, then insert a date as follows:
INSERT INTO
table_name
VALUES ('12-Févr.-2007');
The following example shows the effect of setting
NLS2008LANGUAGE
to French.
1.
In SQL Developer, make a note of the current day and month language in which
Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database,
and then National Language Support Parameters. In the Select Connection dialog
box, select
hr_conn
from the list of connections. The current day and month
language is listed after
NLS2008LANGUAGE
.
2.
Set
NLS2008LANGUAGE
to French.
