Determining NLS_DATE_FORMAT

As a short follow-up to a previous post regarding implicit date conversion and the NLS_DATE_FORMAT, which governs the default date format Oracle will use with the TO_DATE and TO_CHAR functions, I forgot to mention a couple of things:

• how to determine the current NLS_DATE_FORMAT setting
• changing the NLS_DATE_FORMAT via DBMS_SESSION

As noted in the previous post, the NLS_DATE_FORMAT setting is session modifiable via the ALTER SESSION command, e.g.:

ALTER SESSION SET nls_date_format = 'dd/mm/yyyy'

In order to work out what NLS_DATE_FORMAT your session has been configured with then the NLS_SESSION_PARAMETERS view will provide this information:

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

If you wanted to work out what the format was for the database, as opposed to your session, then you can query the NLS_DATABASE_PARAMETERS view instead:

SELECT value
FROM   nls_database_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

NLS_DATE_FORMAT may also be set via the DBMS_SESSION.SET_NLS routine. One advantage to setting it via DBMS_SESSION is that it can done from within PL/SQL without having to go through the messy business of dynamic SQL as ALTER SESSION is not directly callable from PL/SQL. The call is simply:

dbms_session.set_nls ('nls_date_format','''dd/mm/yyyy''')

Note, for string NLS parameter values the embedded quotes need to be contained in the string argument to the procedure call, hence the 3 single quotes on either side of the format parameter.

The following is a basic illustration of the above points, setting the NLS_DATE_FORMAT via DBMS_SESSION and querying the session and database properties:

SQL> EXEC dbms_session.set_nls ('nls_date_format','''dd/mm/yyyy''')

PL/SQL procedure successfully completed.

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

VALUE
----------------------------------------
dd/mm/yyyy

SQL> SELECT value
  2  FROM   nls_database_parameters
  3  WHERE  parameter = 'NLS_DATE_FORMAT'
  4  /

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

Advertisement