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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
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 – Colin 't Hart May 3 '13 at 9:34
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.
share|improve this answer
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
Thanks for the suggestion Adam, will try that out. – Hitesh Chouhan May 15 '13 at 7:06
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 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.