Setting Calendar Definitions
Establishing a Globalization Support Environment
Working in a Global Environment 6-15
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
To set and use NLS_TIMESTAMP_TZ:
The following example sets the NLS_TIMESTAMP_TZ_FORMAT value. It also shows
the format being set explicitly in a SELECT statement, using the
TO_TIMESTAMP_TZ
function.
1.
In SQL Developer, make a note of the current time 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 time format is listed
after
NLS_TIMESTAMP_TZ_FORMAT
.
2.
Set
NLS_TIMESTAMP_TZ_FORMAT
.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM';
3.
Set
NLS_TIMESTAMP_TZ_FORMAT
back to its original setting listed in Step 1. For
example:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR';
Setting Calendar Definitions
This section describes calendar definition.
Overview of Calendar Formats
The following calendar information is stored for each territory:
First Day of the Week
: Some cultures consider Sunday to be the first day of the
week; others consider Monday to be the first day of the week.
The first day of the week is determined by the
NLS_TERRITORY
parameter.
First Calendar Week of the Year
: Some countries use week numbers for
scheduling, planning, and bookkeeping. Oracle supports this convention. In the
ISO standard, the week number can be different from the week number of the
calendar year. For example,
1st
Jan
2005
is in ISO week number 53 of 2004. An
ISO week starts on Monday and ends on Sunday.
To support the ISO standard, Oracle provides the IW date format element. It
returns the ISO week number.
See Also:
Oracle Database Reference for more information on the
NLS_
TIMESTAMP_TZ_FORMAT
parameter
Oracle Database SQL Language Reference for information about date
format models
Oracle Database Globalization Support Guide for information about
date/time data types and time zone support
See Also:
Oracle Database Globalization Support Guide for locale information,
including supported calendars
