NLS_DATE problem

[contactrb]

Hi everybody,

I need a clarification regarding the NLS_DATE parameter.

I have set my NLS_DATE as 'MM/DD/YYYY'. After starting the instance and i verified the values of the parameters through V$parameter as well as
svrmgrl > show parameters nls_date

it display as the above said format.

But at SQL> select sysdate from dual;
displays 02-Apr-01 why?

[pando]

well it should be nls_date_format

setting nls_date_format will only take effect if you client side has NOT set NLS_LANG or NLS_DATE_FORMAT, if any of them is set then the client side predominates and will overwrite the nls_date_format you set in init.ora

you can check

VIEWs

nls_database_parameters
nls_instance_parameters
nls_session_parameters

nls_session_parameters predominates all above it then instance predominates database etc

I bet you have NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 set in your client

[lnr]

Hi,
Why dont use this
If we want to change the NLS_DATE_FORMAT AND SEE


ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
Oracle7 uses the new default date format:


SELECT TO_CHAR(SYSDATE) Today
FROM DUAL
TODAY
-------------------
1993 08 12 14:25:56

Thanks
lnr