Database Administration (MOSC)

MOSC Banner

Setting env variable NLS_TIMESTAMP_FORMAT on UNIX doesn't work

edited Dec 15, 2011 7:11AM in Database Administration (MOSC) 11 commentsAnswered ✓
Hi all,
Oracle versions = AIX (11.1.0.6.0) and Sun (10.2.0.1.0).

export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH:MI:SS.FF'
-> On Unix doesn't work (It works on Windows)
ALTER SESSION SET ...
-> It works on all platform.

Test Case:

export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH:MI:SS.FF'
sqlplus "/as sysdba"

CREATE TABLE TestTbl (c1 NUMBER, c2 TIMESTAMP(6) NOT NULL);
INSERT INTO TestTBL (c1,c2) VALUES (1, CURRENT_TIMESTAMP);


SQL> SELECT C1, TO_CHAR(C2) AS C2 FROM TestTBL;
        C1 C2
---------- ----------------------------------------
         1 13-DEC-11 10.01.48.194137 AM


The output format doesn't appear as 'YYYY-MM-DD HH:MI:SS.FF'? Do I have to set any other NLS parameter for this NLS_TIMESTAMP_FORMAT to work?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center