asked on

NLS_DATE_FORMAT

I am using Oracle 8i and I changed the NLS_DATE_FORMAT='MM/DD/YYYY' in the parameter file(init.ora). And when I check the sysdate from SQL*PLUS, it is still showing the date in DD-MON-YY format. But when I check the sysdate from SVRMGR tool, it is showing me the right format (ie MM/DD/YYYY ). I don't know how to work around this problem.
Note: when I use ALTER SESSION SET NLS_DATE_FORMAT= "MM/DD/YYYY" to change the date format for a particular session from SQL*PLUS, I am getting what I want...

ASKER CERTIFIED SOLUTION

syakobson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bragkumar

ASKER

I appreicate your immediate help. But i got a question for you.. In this case what is the use of NLS_DATE_FORMAT in parameter file. what is for NLS_DATE_FORMAT in init.ora? I am not clear..... From your answer I guess there is no need change to the parameter file. Am I right?

syakobson

Well,
NLS_DATE_FORMAT is not that straight forward. And explanation I gave you is not entirely true. There is a lot of articles in Oracle's MetaLink on that topic. If you are MetaLink customer check http://support.oracle.com.sg/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=74375.1

NLS_DATE_FORMAT is used if a date format mask is not specified in application code. The effective NLS_DATE_FORMAT is determined by the following (in order of precedence):

1. Session NLS_DATE_FORMAT (via ALTER SESSION command)
2. Client side NLS_DATE_FORMAT (from client environment variables/registry settings)
3. Instance NLS_DATE_FORMAT (from init.ora file)
4. Database NLS_DATE_FORMAT

Session NLS_DATE_FORMAT is set to client side NLS_DATE_FORMAT (explicit or implicit) ONLY if NLS_LANG is set. Another words, if NLS_LANG is set, then session NLS_DATE_FORMAT will be taken from the client. If clent NLS_LANG is set and client NLS_DATE_FORMAT is not, session NLS_DATE_FORMAT will default to DD-MON-YY (and that was exactly what you experienced). If NLS_LANG is not specified on the client side, NLS_DATE_FORMAT will be taken from instance NLS_DATE_FORMAT which is NLS_DATE_FORMAT from INIT.ORA. If NLS_DATE_FORMAT is not set in INIT.ORA, session NLS_DATE_FORMAT will default to DD-MON-YY.

Solomon Yakobson.

bragkumar

ASKER

Thanks a lot Solomon!!!