Date and Time Parameters

NLS_DATE_FORMAT

Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and

ALTER SESSION

Default value: Default format for a particular territory
Range of values: Any valid date format mask

This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surrounded by quotation marks. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

To add string literals to the date format, enclose the string literal with double quotes. Note that every special character (such as the double quote) must be preceded with an escape character. The entire expression must be surrounded with single quotes. For example:

NLS_DATE_FORMAT = '\"Today\'s date\" MM/DD/YYYY'

As another example, to set the default date format to display Roman numerals for months, you would include the following line in the initialization file:

NLS_DATE_FORMAT = "DD RM YYYY"

With such a default date format, the following SELECT statement would return the month using Roman numerals (assuming today's date is February 12, 1997):

SELECT TO_CHAR(SYSDATE) CURRDATE FROM DUAL;

CURRDATE
---------
12 II 1997

You can alter the default value of NLS_DATE_FORMAT by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_FORMAT statement.

Date Formats and Partition Bound Expressions

Partition bound expressions for a date column must specify a date using a format that requires the month, day, and 4-digit year to be fully specified. For example, the date format MM-DD-YYYY requires that the month, day, and 4-digit year are fully specified. In contrast, the date format DD-MON-YY (11-jan-97, for example) is invalid because it relies on the current date for the century.

Use TO_DATE to specify a date format which requires the full specification of month, day, and 4-digit year. For example:

TO_DATE('11-jan-1997', 'dd-mon-yyyy')

If the default date format, specified by NLS_DATE_FORMAT, of your session does not support specification of a date independent of the current century (if your default date format is MM-DD-YY for example), then you must take one of the following actions:

Use TO_DATE to express the date in a format that requires you to fully specify the day, month, and 4-digit year.

Change the value of NLS_DATE_FORMAT for the session to support the specification of dates in a format which requires you to fully specify the day, month, and 4-digit year.

NLS_DATE_LANGUAGE

Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and

ALTER SESSION

Default value: Derived from NLS_LANGUAGE
Range of values: Any valid language name

This parameter specifies the language for the spelling of day and month names by the functions TO_CHAR and TO_DATE, overriding that specified implicitly by NLS_LANGUAGE. NLS_DATE_LANGUAGE has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values. For example, to specify the date language as French, the parameter should be set as follows:

NLS_DATE_LANGUAGE = FRENCH

In this case, the query

SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy')
FROM DUAL;

returns

Mercredi:12 Février 1997

Month and day name abbreviations are also in the specified language. For example:

SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy')
FROM DUAL;

Me:12 Fév 1997

The default date format also uses the language-specific month name abbreviations. For example, if the default date format is DD-MON-YYYY, then the above date can be inserted as follows:

INSERT INTO tablename VALUES ('12-Fév-1997');

The abbreviations for AM, PM, AD, and BC are also returned in the language specified by NLS_DATE_LANGUAGE. Note that numbers spelled using the TO_CHAR function always use English spellings. For example:

SELECT TO_CHAR(TO_DATE('12-Fév'),'Day: ddspth Month')
FROM DUAL;

returns:

Mercredi: twelfth Février

You can alter the default value of NLS_DATE_LANGUAGE by changing its value in the initialization parameter file and then restarting the instance. You can alter the value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE statement.

Time Formats

NLS_TIMESTAMP_FORMAT

Parameter type: String
Parameter scope: Dynamic, Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Derived from NLS_TERRITORY
Range of values: Any valid datetime format mask

NLS_TIMESTAMP_FORMAT defines the default timestamp format to use with TO_CHAR and TO_TIMESTAMP functions. The value must be surrounded by quotation marks as follows:

NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

An example is:

TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')

You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter file and then restarting the instance. To alter the value during a session, use the ALTER SESSION SET statement.

NLS_TIMESTAMP_TZ_FORMAT

Parameter type: String
Parameter scope: Dynamic, Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Derived from NLS_TERRITORY
Range of values: Any valid datetime format mask

NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format to use with TO_CHAR and TO_TIMESTAMP_TZ functions. The value must be surrounded by quotation marks as follows:

NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'

An example is:

TO_TIMESTAMP_TZ('2000-08-20, 05:00:00.55 America/Los_Angeles', 'yyyy-mm-dd hh:mi:ss.ff TZR')

You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

You can also alter the value of NLS_TIMESTAMP_TZ_FORMAT by changing its value in the initialization parameter file and then restarting the instance. To alter the value during a session, use the ALTER SESSION SET statement.

Time Zone Parameters for Databases

A displacement from UTC (Coordinated Universal Time, formerly Greenwich Mean Time). The following example sets the time zone of the database to Pacific Standard time (eight hours behind UTC):

CREATE DATABASE ... SET TIME_ZONE = '-08:00 ';

A time zone region. The following example also sets the time zone of the database to Pacific Standard time in the United States:

CREATE DATABASE ... SET TIME_ZONE = 'PST ';

To see a listing of valid region names, query the V$TIMEZONE_NAMES view.

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the SET TIME_ZONE clause, then Oracle uses the operating system's time zone of the server. If the operating system's time zone is not a valid Oracle time zone, the database time zone defaults to UTC.

After the database has been created, you can change the time zone by issuing the ALTER DATABASE SET TIME_ZONE statement and then shutting down and starting up the database. The following example sets the time zone of the database to London time:

ALTER DATABASE SET TIME_ZONE = 'Europe/London ';

To find out the time zone of a database, use the DBTIMEZONE function as shown in the following example:

SELECT dbtimezone FROM dual;

DBTIME
-------
-08:00

Time Zone Parameters for Sessions

O/S Local Time Zone

ALTER SESSION SET TIME_ZONE = local;

Database Time Zone

ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

An absolute time difference

ALTER SESSION SET TIME_ZONE = '-05:00';

A named region

ALTER SESSION SET TIME_ZONE = 'America/New_York';

You can use the environment variable ORA_SDTZ to set the default client session time zone. This variable takes input like DB_TZ, OS_TZ, time zone region or numerical time zone offset. If ORA_SDTZ is set to DB_TZ, the session time zone will be the same as the database time zone. If it is set to OS_TZ, the session time zone will be same as the operating system's time zone. If ORA_SDTZ is set to an invalid Oracle time zone, Oracle uses the operating system's time zone as default session time zone. If the operating system's time zone is not a valid Oracle time zone, the session time zone defaults to UTC. To find out the time zone of a user session, use the SESSIONTIMEZONE function as shown in the following example:

SELECT sessiontimezone FROM dual;

SESSIONTIMEZONE 
---------------
         -08:00