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/