How To Set NLS_DATE_FORMAT Parameter In Oracle

NLS_DATE_FORMAT is one of the initialization parameter in Oracle.

What are The Initialization parameter in Oracle?

Initialization parameter in oracle defines the behavior of the database.
From DBA perspective, these parameters can be used to improve the performance of the database.

Today we will focus on the parameter: NLS_DATE_FORMAT

• This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions.
• The value of this parameter can be any valid date format mask & it should be in the double quotation mark.

For e.g.:

NLS_DATE_FORMAT = "MM/DD/YYYY"

Parameter in oracle NLS_TERRITORY defines the Default value of this parameter.

Property	Description
Parameter type	String
Parameter scope	Initialization parameter, environment variable, and ALTER SESSION
Default value	Derived from NLS_TERRITORY
Range of values	Any valid date format mask

Get the default value of parameter NLS_DATE_FORMAT.

We can find the default value of parameter NLS_DATE_FORMAT by executing below query:

SELECT * FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

Query will displayed output as:

NLS_DATE_FORMAT = "MM/DD/YYYY"

Set the value of parameter: NLS_DATE_FORMAT.

We can use below query to change the value of the parameter for a specific session:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

NOTE:
Once this session is killed, the value of the set parameter will no longer be valid.
It will be overwritten by the Default value or by the client-side value .

References:
https://docs.oracle.com/