Re: Set NLS_DATE in oracle

evoradba_at_yahoo.ca wrote:
> I have oracle installation on linux Oracle 9i now I need to set the
> NLS_DATE to display the hour and minutes I have tried everytime
> possible, but it seems that there is something else missing,Im not
> using the spfile, only the init.ora, this is my configuration,
> SQL> select name,value from v$parameter where name='nls_date_format';
> nls_date_format
> DD-MON-YYYY HH24:MI:SS
> SQL> select sysdate from dual;
>
> SYSDATE
> ---------------
> 26-OCT-05
>
> SQL> select name,value from v$parameter where name='nls_date_format';
>
> Thank you
> Maria
> nls_date_format
> DD-MON-YYYY HH24:MI:SS
>
>
> SQL> select name,value from v$parameter2 where name='nls_date_format';
>
> nls_date_format
> DD-MON-YYYY HH24:MI:SS
>
>
> SQL> select name,value from v$spparameter where name='nls_date_format';
> nls_date_format
>
> SQL> select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
>
> NLS_DATE_FORMAT
> DD-MON-RR
>
>
> DD-MON-YYYY HH24:MI:SS
> SQL> select name, value from v$system_parameter where
> name='nls_date_format';
>
> nls_date_format
> DD-MON-YYYY HH24:MI:SS
>
> this is the init.ora
>
> nls_date_format='DD-MON-YYYY HH24:MI:SS'
> I have also bounced oracle counteless times and no luck, and this works
> per session, but I need on server wide
>
> what do I need to do so that the select sysdate from dual will display
>
> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
>
> Session altered.
>
> SQL> select sysdate from dual;
>
> SYSDATE
> --------------------------
> 26-OCT-2005 21:33:00
maybe reading the nls concept could help ?

in short, serverside (instance-level) settings have lower priorty than client
side settings which are usually derived from nls_lang in the enviroment (not talking here jdbc, different story). In other word's, instance level settings
are only "active" if the client doesn't override them.

If you want a specific time format at your client, you could export nls_date_format (at the client):

export NLS_DATE_FORMAT='...

or set it at your bashrc.  

regards

s.kapitza Received on Thu Oct 27 2005 - 07:58:59 CDT