About the NLS_DATE_FORMAT Parameter

Specifies: Default date format to use with the TO_CHAR and TO_DATE functions (which are introduced in "Using Conversion Functions in Queries").

Acceptable Values: Any any valid datetime format model. For example:

NLS_DATE_FORMAT='MM/DD/YYYY'

For information about datetime format models, see Oracle Database SQL Language Reference.

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

The default date format might not correspond to the convention used in a given territory. To get dates in localized formats, you can use the 'DS' (short date) and 'DL' (long date) formats.

The following example shows how setting NLS_TERRITORY to AMERICA and FRANCE affects the default, short, and long date formats.

To try this example in SQL Developer, enter the statements and queries in the SQL Worksheet. For information about the SQL Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

NLS_TERRITORY Affects Date Formats

Note the current value of NLS_TERRITORY.

For instructions, see "Viewing NLS Parameter Values".
If the value in step 1 is not AMERICA, change it:
```
ALTER SESSION SET NLS_TERRITORY=AMERICA;
```

Run this query:

SELECT hire_date "Default",
       TO_CHAR(hire_date,'DS') "Short",
       TO_CHAR(hire_date,'DL') "Long"
FROM employees
WHERE employee_id IN (111, 112, 113);

Result:

Default    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
 
3 rows selected.

Change the value of NLS_TERRITORY to FRANCE:

ALTER SESSION SET NLS_TERRITORY=FRANCE;

Repeat the query from step 3.

Result:

Default  Short      Long
-------- ---------- ---------------------------
30/09/97 30/09/1997 tuesday 30 september 1997
07/03/98 07/03/1998 saturday 7 march 1998
07/12/99 07/12/1999 tuesday 7 december 1999
 
3 rows selected.

(To get the names of the days and months in French, you must set either NLS_LANGUAGE or NLS_DATE_LANGUAGE to FRENCH before running the query.)

Set NLS_TERRITORY to the value that it had at step 1.

The following example changes the value of NLS_DATE_FORMAT, overriding the default value set by NLS_TERRITORY.

NLS_DATE_FORMAT Overrides NLS_TERRITORY

Note the current values of NLS_TERRITORY and NLS_DATE_FORMAT.

For instructions, see "Viewing NLS Parameter Values".
If the valueof NLS_TERRITORY in step 1 is not AMERICA, change it:
```
ALTER SESSION SET NLS_TERRITORY=AMERICA;
```
If the valueof NLS_DATE_FORMAT in step 1 is not 'Day Month ddth', change it:
```
ALTER SESSION SET NLS_DATE_FORMAT='Day Month ddth';
```

Run this query (from previous example, step 3):

SELECT hire_date "Default",
       TO_CHAR(hire_date,'DS') "Short",
       TO_CHAR(hire_date,'DL') "Long"
FROM employees
WHERE employee_id IN (111, 112, 113);

Result:

Default                  Short      Long
------------------------ ---------- -----------------------------
Tuesday   September 30th 9/30/1997  Tuesday, September 30, 1997
Saturday  March     07th 3/7/1998   Saturday, March 07, 1998
Tuesday   December  07th 12/7/1999  Tuesday, December 07, 1999

3 rows selected.

Set NLS_TERRITORY and NLS_DATE_FORMAT to the values that they had at step 1.

About the NLS_DATE_FORMAT Parameter

Related Topics