CODEWITHSUNDEEP
oracle-databaseformattimestampdefault
xiaohan2012
xiaohan2012

Reputation: 10332

How to change the Oracle default timestamp format

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?

Upvotes: 1

Views: 22112

Answers (2)

Clark
Clark

Reputation: 42

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.)

Upvotes: 0

Ollie
Ollie

Reputation: 17538

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.

Upvotes: 3

Related Questions