Date and Time Parameters

NLS_DATE_FORMAT Parameter

The NLS_DATE_FORMAT parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The NLS_TERRITORY parameter determines the default value of the NLS_DATE_FORMAT parameter. The value of NLS_DATE_FORMAT can be any valid date format model. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

The Oracle default date format may not always correspond to the cultural-specific convention used in a given territory. You can use the short date and long date format in SQL, using the 'DS' and 'DL' format models, respectively, to obtain dates in localized formats. The examples in this section show the differences among some of the date formats.

Example: Using the Default, Short, and Long Date Formats shows the use of the default, short, and long date formats.

-- Use an ALTER SESSION statement to change the territory to America, 
-- and the language to American
ALTER SESSION SET NLS_TERRITORY = America NLS_LANGUAGE = American;

-- After the session is altered, select the dates with 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 results of the query in Example: Using the Default, Short, and Long Date Formats should be similar to the following:

To add string literals to the date format, enclose the string literal with double quotes. Note that when double quotation marks are included in the date format, the entire value must be enclosed by single quotation marks. For example:

NLS_DATE_FORMAT = '"Date: "MM/DD/YYYY'

NLS_DATE_LANGUAGE Parameter

The NLS_DATE_LANGUAGE parameter specifies the language for the day and month produced by the TO_CHAR and TO_DATE functions. NLS_DATE_LANGUAGE overrides the language that is specified implicitly by NLS_LANGUAGE. The NLS_DATE_LANGUAGE parameter has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values.

The NLS_DATE_LANGUAGE 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 (NLS_DATE_FORMAT)

Example: Setting NLS_DATE_LANGUAGE=FRENCH: Month and Day shows how to use NLS_DATE_LANGUAGE to set the date language to French.

-- set NLS_DATE_LANAGUAGE for this user session
ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;

-- display the current system date
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;

The output from the example should be similar to the following, depending on the current system date:

The default date format uses the month abbreviations determined by the NLS_DATE_LANGUAGE parameter. For example, if the default date format is DD-MON-YYYY and NLS_DATE_LANGUAGE = FRENCH, then insert a date as follows:

INSERT INTO table_name VALUES ('12-Févr.-1997');

NLS_TIMESTAMP_FORMAT Parameter

The NLS_TIMESTAMP_FORMAT parameter defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes. The NLS_TERRITORY parameter determines the default value of NLS_TIMESTAMP_FORMAT. The value of NLS_TIMESTAMP_FORMAT can be any valid datetime format model.

The following example shows a value for NLS_TIMESTAMP_FORMAT:

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

NLS_TIMESTAMP_TZ_FORMAT Parameter

The NLS_TIMESTAMP_TZ_FORMAT parameter defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes. It is used with the TO_CHAR and TO_TIMESTAMP_TZ functions. The NLS_TERRITORY parameter determines the default value of the NLS_TIMESTAMP_TZ_FORMAT parameter. The value of NLS_TIMESTAMP_TZ_FORMAT can be any valid datetime format model.

The format value must be surrounded by quotation marks. For example:

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

In Example: Setting NLS_TIMESTAMP_TZ_FORMAT the TO_TIMESTAMP_TZ function uses the format value that was specified for NLS_TIMESTAMP_TZ_FORMAT.

-- display August 20, 2005 using the format of NLS_TIMPSTAMP_TZ_FORMAT
SELECT TO_TIMESTAMP_TZ('2005-08-20, 05:00:00.55 America/Los_Angeles', 
  'yyyy-mm-dd hh:mi:ss.ff TZR') "TIMESTAMP_TZ Format" FROM DUAL;

The output from the example should be similar to the following: