Check the NLS date format in Oracle

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.