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).

So, to change the date format for your session, you can either update the NLS_TERRITORY parameter, or explicitly update each parameter individually.

This article provides examples of checking and changing these values, and viewing the results.

Check the Initial Setting

First, let’s check my current setting for the NLS_DATE_FORMAT parameter:

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              AUSTRALIA                         
NLS_DATE_FORMAT            DD/MON/RR                         
NLS_TIMESTAMP_FORMAT       DD/MON/RR HH12:MI:SSXFF AM        
NLS_TIMESTAMP_TZ_FORMAT    DD/MON/RR HH12:MI:SSXFF AM TZR    

The value of my NLS_TERRITORY parameter is AUSTRALIA, and so the various datetime parameters use the default format model for Australia.

This value affects 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:

04/AUG/21

Change the Setting

We can use the ALTER SESSION statement to change the setting for these parameters.

We have the option of changing each of these parameters individually, or changing the NLS_TERRITORY parameter, which automatically updates each of those parameters implicitly.

Change the NLS_TERRITORY Parameter

Here’s an example of changing the NLS_TERRITORY parameter:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

Result:

Session altered.

Let’s check the new values:

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    

So we can see that, not only has the territory been updated, the various datetime parameters have also been updated.

Change Just the NLS_DATE_FORMAT Parameter

Here’s an example of updating the NLS_DATE_FORMAT parameter:

ALTER SESSION SET NLS_DATE_FORMAT = 'rr-mm-dd';

Result:

Session altered.

Let’s check the new value:

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            rr-mm-dd                        
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM        
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR    

As expected, only the NLS_DATE_FORMAT parameter has been changed to the new format. The others remain unchanged.

Now when we run SYSDATE, the date is returned in our new format, but SYSTIMESTAMP returns it using the NLS_TIMESTAMP_TZ_FORMAT format, which is unchanged:

SELECT 
    SYSDATE,
    SYSTIMESTAMP
FROM DUAL;

Result:

    SYSDATE                              SYSTIMESTAMP 
___________ _________________________________________ 
21-08-09    09-AUG-21 07.22.35.402453000 PM -04:00