Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

Existing queries that rely on this format may return different results.

Check if your existing code interprets TIMESTAMP fields in a way that is format-dependent, and be sure to update it if it does. Note also that NLS_TIMESTAMP_FORMAT can be set at the database or client level, so take note of what format a client sees when it executes code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.