Tag Archives: NLS DATE FORMAT

Set NLS date format in sqlplus

Check and set the default data format in Oracle

NLS DATE FORMAT is describe the default format in Oracle
You can change it at session level or system level.

Check the default date format

SQL> SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

VALUE
------------------------
DD-MON-RR

Change DATE format at session level

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

Check all the Default NLS setting in Oracle

show parameter NLS

NAME                    TYPE        VALUE
----------------------- ----------- ------------
nls_calendar            string      GREGORIAN
nls_comp                string      BINARY
nls_currency            string      $
nls_date_format         string      DD-MON-RR
nls_date_language       string      AMERICAN
nls_dual_currency       string      $
nls_iso_currency        string      AMERICA
nls_language            string      AMERICAN
nls_length_semantics    string      BYTE
nls_nchar_conv_excp     string      FALSE
nls_numeric_characters  string      .,
nls_sort                string      BINARY
nls_territory           string      AMERICA
nls_time_format         string      HH.MI.SSXFF AM
nls_time_tz_format      string      HH.MI.SSXFF AM TZR
nls_timestamp_format    string      DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string      DD-MON-RR HH.MI.SSXFF AM TZR