Re: Default Date formatting with NLS_DATE_FORMAT

What you did should have changed the database format - However if the client
has any NLS_ parameters in its registry ( or ini or whatever your system's clients use to save user preferences ) this will override ALL NLS_ parameters, substituting default values for any NLS_ not set on the client....so a query like
select sysdate from dummy
will return a MM/DD/YY formatted date even if the database format is YYYY/MM/DD (MM'DD/YY is Oracle's default date format )  

The client setting( if present ) controls what you get.......

I eliminate ALL NLS_ parameters on my clients so that the database settings ( from init(sid).ora ) control

The confusion is caused by the fact that there are 3 types of data format areas Database_value - from init(sid).ora
Session_value - from same unless 'alter session set ....' Instance_value - from client settings unless none are set then uses database_values

Hope it doesn't just add to the confusion...

You might want to search dejanews for other postings on this issue - there have been many,many of them.....It can be very confusing....

John Greco
Oracle DBA/Application Developer  

Ilias Santouridis <Ilias.Santouridis_at_eurodyn.com> wrote:

>Hi all.
>
>We want to change our database's default date formatting.
>The current format is "DD-MON-YY", which I guess results from
>the value of NLS_TERRITORY which is "AMERICA".
>
>I want to change the date format to "YYYY/MM/DD".
>
>I've tried to change the value of NLS_DATE_FORMAT parameter.
>When I execute :
>
> alter session set NLS_DATE_FORMAT = "YYYY/MM/DD"
>
>the format changes for the current session.
>
>However, I want to make this format the default one for all sessions.
>The manual suggests to make an entry in the init.ora file
>for the NLS_DATE_FORMAT parameter. I included the following entry in
>our database's init.ora file :
>
>nls_date_format="YYYY/MM/DD"
>
>The database was then shutted down and restarted. However, the default
>date format didn't change and remained "DD-MON-YY".
>Any explanations ?
>
>I would like to thank you in advance.
>Ilias

To reply please remove the 'nospam' part of the address Received on Fri Feb 19 1999 - 09:24:19 CST