Oracle Quick Tip: Setting the Default Date and Timestamp Format for a Session

The default DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE format models for a session can be altered with the following commands.

ALTER SESSION SET nls_date_format = [format model];
ALTER SESSION SET nls_timestamp_format = [format model];
ALTER SESSION SET nls_timestamp_tz_format = [format model];

For example.

ALTER SESSION SET nls_date_format='DD-Mon-RR HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format='DD-Mon-RR HH24.MI.SSXFF3';
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-RR HH:MI.SSXFF6 TZR';
 
SELECT current_date, localtimestamp, current_timestamp FROM dual;
 
CURRENT_DATE        LOCALTIMESTAMP          CURRENT_TIMESTAMP 
------------------- ----------------------- ----------------------------
09-Feb-09 12:57:43  09-Feb-09 12.57.42.787  09-Feb-09 12:57.42.787953 GB
 
1 rows selected

Alternatively, if you are using SQL Developer, select Tools -> Preferences, expand Database and select NLS Parameters.

The V$NLS_PARAMETERS view contains the current values of the NLS parameters.

SELECT *
FROM v$nls_parameters
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT');

PARAMETER                 VALUE
------------------------- -----------------------------
NLS_DATE_FORMAT           DD-Mon-RR HH24:MI:SS
NLS_TIMESTAMP_FORMAT      DD-Mon-RR HH24.MI.SSXFF3
NLS_TIMESTAMP_TZ_FORMAT   DD-Mon-RR HH24:MI.SSXFF TZR
 
3 rows selected