Consequences of changing NLS_TIMESTAMP_FORMAT

I administer a 9.2.0.8 database where the NLS_TIMESTAMP_FORMAT is set to 'DD-MON-RR HH.MI.SSXFF AM';.

This leads to odd results when using the CAST TO TIMESTAMP function:

select CAST('14-SEP-2011' AS TIMESTAMP) "DATE" from dual;
----------
14-SEP-2020 11:00:00.000000 AM

If I alter the NLS_TIMESTAMP_FORMAT to 'DD-MON-YYYY HH.MI.SSXFF AM' then I am good to go.

Would changing the NLS_TIMESTAMP_FORMAT parameter on the database to YYYY have any consequences?

The database-level NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameters are not particularly useful because they are always overridden by the client settings. So even if you change the database setting, 99+% of the time when a user comes along and connects to the database, their session is going to set a NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT based on the client's NLS_LANG or other national language settings (i.e. applications using the thin JDBC driver use the JVM's internationalization settings rather than relying on the client NLS_LANG) and overrule the setting you made in the database. There are a few corner cases involving database jobs using DBMS_JOBS or DBMS_SCHEDULER where there is no client where the database NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT are used, I believe, but those are rather rare.

You could create a login trigger that did an ALTER SESSION to set the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT for your sessions. That would overrule the settings the client requested when it created the session. As Nick points out, you'd have to worry that other queries are relying on the current setting for implicit conversions and your change might break that code.

In general, you're much better off avoiding implicit conversions wherever possible but particularly when it comes to converting strings to or from dates and timestamps simply because there are so many different formats that you're setting yourself up for problems. If you want to specify a date literal in your code, you're much better using the ANSI date and timestamp syntax

SQL> ed
Wrote file afiedt.buf

  1  select cast( date '2011-09-14' as timestamp ) dt,
  2         timestamp '2011-09-14 13:15:30' ts
  3*   from dual
SQL> /

DT                             TS
------------------------------ -----------------------------------
14-SEP-11 12.00.00.000000 AM   14-SEP-11 01.15.30.000000000 PM

If you want to convert a string to a date or a timestamp, you're better off using a TO_DATE or a TO_TIMESTAMP with an explicit format mask.

StackExchange Database Administrators Q#5778, answer score: 4