When working with functions that return dates, Oracle Database returns these dates based on the value of the NLS_DATE_FORMAT parameter.

There is also an NLS_TIMESTAMP_FORMAT parameter and an NLS_TIMESTAMP_TZ_FORMAT parameter, both of which have a datetime format mask that can be specified separately.

All of these parameters have their default values derived from the NLS_TERRITORY parameter (which by default, is operating system-dependent).

Check the Settings

We can query the V$NLS_PARAMETERS view to check the current values of these parameters (and other NLS parameters):

SELECT
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_TERRITORY',
    'NLS_DATE_FORMAT',
    'NLS_TIMESTAMP_FORMAT',
    'NLS_TIMESTAMP_TZ_FORMAT'
    );

Result:

                 PARAMETER                           VALUE 
__________________________ _______________________________ 
NLS_TERRITORY              AMERICA                         
NLS_DATE_FORMAT            DD-MON-RR                       
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM        
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR    

The value of my NLS_TERRITORY parameter is AMERICA, and the various datetime parameters use the default format model for that territory.

These values affect how the date is formatted when we use certain functions. For example, here’s an example of using SYSDATE to return the current date:

SELECT SYSDATE
FROM DUAL;

Result:

09-AUG-21

We can change the value of the NLS parameters in the session scope if required. See How to Change the Date Format in your Oracle Session for examples.

Other NLS Parameters

The V$NLS_PARAMETERS contains other parameters in addition to the ones listed above. Here’s an example of returning all rows and columns in the view:

SELECT *
FROM V$NLS_PARAMETERS;

Result:

                 PARAMETER                           VALUE    CON_ID 
__________________________ _______________________________ _________ 
NLS_LANGUAGE               AMERICAN                                3 
NLS_TERRITORY              AMERICA                                 3 
NLS_CURRENCY               $                                       3 
NLS_ISO_CURRENCY           AMERICA                                 3 
NLS_NUMERIC_CHARACTERS     .,                                      3 
NLS_CALENDAR               GREGORIAN                               3 
NLS_DATE_FORMAT            DD-MON-RR                               3 
NLS_DATE_LANGUAGE          AMERICAN                                3 
NLS_CHARACTERSET           AL32UTF8                                3 
NLS_SORT                   BINARY                                  3 
NLS_TIME_FORMAT            HH.MI.SSXFF AM                          3 
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM                3 
NLS_TIME_TZ_FORMAT         HH.MI.SSXFF AM TZR                      3 
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR            3 
NLS_DUAL_CURRENCY          $                                       3 
NLS_NCHAR_CHARACTERSET     AL16UTF16                               3 
NLS_COMP                   BINARY                                  3 
NLS_LENGTH_SEMANTICS       BYTE                                    3 
NLS_NCHAR_CONV_EXCP        FALSE                                   3 

There are also other NLS parameters that aren’t listed in this view (such as NLS_LANG, NLS_CREDIT, and NLS_DEBIT), but they can only be set as an environment variable (i.e. not in the session).

NLS parameters can be set in various places, such as at the database level, in an initialization parameter file, in environment variables, at the session level, and even within some functions. Each of these has a predetermined priority in relation to the others.

The values in the session scope (as outlined in this article) override all other scopes, except for when it’s explicitly specified as an argument when calling a function.

See Methods of Setting NLS Parameters and their Priorities for a table outlining the precedence order each scope has in relation to the others.

Also see How to Check the Values of the NLS Parameters, which covers the various views that you can use to return the values from each scope.