I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd.

Does anyone have any clue why this is happening?

  • 1
    When is it changing automatically? – Colin 't Hart May 2 '13 at 7:27
  • When is it differing? Are you aware that tools/libraries are able to change it with an "alter session" and this may be happening in the background (depending on the tool), upon login for example. – Philᵀᴹ May 2 '13 at 7:53
  • I am using sqlplus to connect, the nls_timestamp_format parameter is changing randomly from dd-mon-rr to yyy/mm/dd. – Hitesh Chouhan May 2 '13 at 10:53
  • 1
    If it's only after each login in SQL*Plus then the most logical place it's happening is in glogin.sql or login.sql (as haki mentions in his answer). See also adp-gmbh.ch/ora/sqlplus/login.html – Colin 't Hart May 3 '13 at 9:34
  • 3
    Never rely on the system default for NLS values. Always specify them as part of the session creation or in the code itself. – David Aldridge May 4 '13 at 9:54

First change the parameter at the system level:

alter system set nls_timestamp_format = 'dd/mm/yyyy' scope='both';

This will also update the spfile so even if you bounce the db you'll keep your definition.

Second, check for any specific definitions in your client. If you're using windows go to

  1. the registry - check if you have the nls_timestamp_format set differently.
  2. the glogin.sql file - this is a file that executes every time you log into sqlplus. there might be an alter session... there that changes the system setting.
  • Thanks all, for your suggestions. The only thing that i am not able to understand is that why does it changes randomly?? – Hitesh Chouhan May 6 '13 at 8:08
  • One can also set NLS_TIMESTAMP_FORMAT in an AFTER LOGON trigger; don't forget to check that. – Adam Musch May 10 '13 at 22:24
  • Hi All, I got to the root of the problem. The problem was that every time i log into the database it was switching on different instances and on one of the instances the variable NLS_TIMESTAMP_FORMAT was different, so everytime i got connected to that instance NLS_TIMESTAMP_FORMAT would differ from the other ones. – Hitesh Chouhan Jun 13 '13 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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