Re: NLS_DATE_FORMAT

Once again i had forgot to describe my environment. I think i should put it in my signature so i don't forget nomore.....

Oracle : 7.3.4
Server : HP-UX 10.20
Client : WinNT, Win95 and UNIX

On the Win client side, every one of them have their registry entry set. I need to put it on the server side (UNIX) because of the batch running directly on UNIX.

This is my test "select" statement :

select TO_CHAR(TO_DATE('01-JAN-00'),'YYYY-MM-DD') dt from dual;

It should return "2000-01-01" and not "1900-01-01".... but it does.

I will change my init.ora to the exact string you sumitted, mine was a little different. But Oracle did not complaint about mine and showed it in the alert log properly...

I'll get back to u next week since i can only bounce the DB on week-ends.

Thanks,

Marc Mazerolle
InforMaze Technologies

Thomas Kyte wrote:

> A copy of this was sent to Marc Mazerolle <informaze_at_sympatico.ca>
> (if that email address didn't require changing)
> On Wed, 23 Jun 1999 16:44:45 GMT, you wrote:
>
> >Hi all,
> >
> >What is the syntax to set the NLS_DATE_FORMAT to "DD-MON-RR" in the
> >init.ora. file ? I know there is a specific syntax to follow....
> >
> >Regards,
> >
> >Marc Mazerolle
>
> It would look like:
>
> nls_date_format = "DD-Mon-RR"
>
> but bear in mind.... If the client sets the NLS_* parameters -- they override
> the server in all cases. In fact, if the client sets the NLS_LANG parameter --
> that causes all NLS_* settings on the server to be ignored and the defaults for
> that NLS_LANG specified on the client on used.
>
> Where that comes into play is typically if the client is windows and the server
> is unix. The client install on windows set the NLS_LANG parameter in the
> registry by default. This setting is different from the default you find for
> unix. Any NLS_* settings you put in the init.ora will *not* be used by those
> clients.
>
> You have to set the NLS_DATE_FORMAT in the registry on the client
>
> or
>
> you need to put an "alter session set nls_date_format='yyyymmdd'" in your apps
> right after the connect
>
> or
>
> with Oracle8i, release 8.1 you can code:
>
> create or replace trigger data_logon_trigger
> after logon
> ON DATABASE
> begin
> execute immediate
> 'alter session set nls_date_format = ''yyyymmdd'' ';
> end;
> /
>
> as well.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Jun 23 1999 - 13:30:25 CDT