Oracle – Working with NLS_DATE_FORMAT

In Oracle SQL, it can get a little tiring constantly using the TO_DATE() and TO_CHAR() functions when working with dates. NLS_DATE_FORMAT can be very handy when working with the DATE datatype. By setting a format for NLS_DATE_FORMAT, the string format for dates will be automatically handled. Let’s say we want to work with dates consistently in a ‘DD-MON-YYYY HH24:MI:SS' format:

SQL> desc test_tab;
 Name                  Null?    Type
 --------------------- -------- ----------------------------
 DATE_COL                       DATE

SQL> SELECT date_col FROM test_tab;

DATE_COL
---------
05-MAR-14

SQL> SELECT TO_CHAR( date_col, 'DD-MON-YYYY HH24:MI:SS' ) FROM test_tab;

TO_CHAR(DATE_COL,'DD-MON-YYYY
-----------------------------
05-MAR-2014 23:12:22

SQL> INSERT INTO test_tab VALUES ( '04-FEB-2013 03:21:11' );
INSERT INTO test_tab VALUES ( '04-FEB-2013 03:21:11' )
                              *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO test_tab
 2 VALUES ( TO_DATE( '04-FEB-2013 03:21:11', 'DD-MON-YYYY HH24:MI:SS' ) );

1 row created.

Without using TO_DATE() and TO_CHAR(), we are not able to work in our desired format. Let’s make life a little easier and use the ALTER SESSION SET NLS_DATE_FORMAT command:

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> SELECT date_col FROM test_tab;

DATE_COL
--------------------
05-MAR-2014 23:12:22

SQL> INSERT INTO test_tab
 2 VALUES ( '04-FEB-2013 03:21:11' );

1 row created.

To find what the current NLS_DATE_FORMAT string is, use
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT':

SQL> SELECT value
  2  FROM nls_session_parameters
  3  WHERE parameter = 'NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------
DD-MON-YYYY HH24:MI:SS
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s