1

Using alter session only set NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT for the current session.

It does not change the default database properties.

The Oracle 10g's locale installed on my machine is set to Chinese. And the timestamp format is Chinese specific.

I need to use the US format (YYYY-MM-DD HH:MI:SS).

Any solution?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
xiaohan2012
  • 9,870
  • 23
  • 67
  • 101
  • This is a *client* settings, each client application may have a different format. You should be more specific and tell us which client/app you use (as per your latest question it seems to be jdbc/hibernate) – Vincent Malgrat Aug 22 '11 at 13:52
  • xiaohan2012, could you clarify your question a little please? Are you trying to change the NLS settings for the server, for your client PC or for all client PC's? Any other information would be handy too. – Ollie Aug 22 '11 at 14:29
  • Hi, Vincent and Ollie, my case is like this: I am using hibernate as the persistence layer and Oracle as the underlying database. And I have installed oracle 10203 on my win7. The locale setting of oracle is Chinese which may cause the timestamp format to be Chinese. And the Chinese timestamp is the problem. If I use hibernate to retrieve a record from oracle, the timestamp property of some entity will be in Chinese form(because of the locale setting in Oracle, I think). @Vincent Malgrat – xiaohan2012 Aug 22 '11 at 14:45
  • And the Chinese format will cause setting timestamp property in java code fail, and passing **null** to those property.@Vincent Malgrat @Ollie – xiaohan2012 Aug 22 '11 at 14:45
  • 1
    xiaohan2012, here is a link with info on hibernate and Oracle date issues: https://forum.hibernate.org/viewtopic.php?p=2407493 but if your database is local to your PC then using the links from my answer you can change the database parameters and/or also your client NLS settings. i can't help more with hibernate as I do not know much about it. – Ollie Aug 22 '11 at 14:58
  • 1
    @xiaohan2012, have you tried `Locale.setDefault(Locale.ENGLISH)`, as per suggested in ([NLS_LANG setting for JDBC thin driver?](http://stackoverflow.com/questions/1079985/nls-lang-setting-for-jdbc-thin-driver/1306042#1306042))? – Vincent Malgrat Aug 22 '11 at 15:01
  • No, I have not. I do not know where to put this line in my java code. Any suggestion? – xiaohan2012 Aug 22 '11 at 15:31
  • Before opening a connection. I think most NLS session parameters set by jdbc depend upon this variable. – Vincent Malgrat Aug 22 '11 at 15:54
  • Calling YYYY-MM-DD HH:MI:SS a "US format" is ... unconventional. Perhaps it is that Internet/RFC/mail date/time format? – Peter Mortensen Aug 25 '19 at 19:48
  • What is *"alter session"*? – Peter Mortensen Aug 25 '19 at 19:49

2 Answers2

3

From the Oracle documentation:

You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.

NLS_TIMESTAMP_FORMAT http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams132.htm

and for NLS_TIMESTAMP_TZ_FORMAT: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams133.htm

You'll need to alter the instance's paramters and bounce the database.

Ollie
  • 17,058
  • 7
  • 48
  • 59
  • 2
    This will only modify the instance default parameters. The client parameters will overwrite the instance parameters and the results will still be in chinese – Vincent Malgrat Aug 22 '11 at 13:50
  • Vincent, you are correct but in the link (and in my answer) it mentions that for the client you can secify the NLS_TIMESTAMP_X as a client environment variable. – Ollie Aug 22 '11 at 13:53
  • Your answer is technically correct but I don't think it will solve the OP's problem :) +1 though – Vincent Malgrat Aug 22 '11 at 14:00
  • I think I might have a different interpretation of the OP's question. I'll add a comment asking them to clarify. Hopefully then we can get them a clear answer. – Ollie Aug 22 '11 at 14:28
0

Doh. I had two tabs open and put this in the wrong thread...

To set the default permanently in the database, from the database:

alter system ... scope=spfile;

then as SYS (so you may need to talk to a DBA), and obviously at an appropriate time! -

shutdown;
startup;

(I know the spfile has been mentioned, but it can all be done from the sqlplus cmdline, at least in version 11g.)

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Clark
  • 21
  • 3