NLS_DATE_FORMAT

what are the CONS/IMPLICATIONS of changing default parameter NLS_DATE_FORMAT from MMDDYY to MMDDYYYY.

What happens when couple of databases in local /remote network talk to each other with diffrent NLS_DATE_FORMAT.

I shouldn't change that parameter per our shop Database Administration Services policies and procedures. Seems developers have to change lots of their source, if format can't be changed. Couldn't convince 'em. Any help out there.

Hi

I thin nls_date_format going to change the format as per ur request in the server but its going to effect over all who ever acess the that server .

We can always change the format ,bascially when user wants to store in different date formats he needs to use to_date and to_char for formatiing .

I dont think we need to change nls_date_format

Thanks
lnreddy

I know that I don't need to change that parameter. Here the Question of what if I change ??

I have already told 'em I am not gonna change it anf asked to use built in functions to_char/to_date and they complain they have to change lots of their source if it can't be changed to MMDDYYYY.

If we are talking about application developers, they can always change it at the session level. They could have the app automatically alter the session immediately after connecting and thereby have the benefits they request without the implications you'd like to avoid.

Just a thought, because I'm not sure what all the implications are :),

- Chris

I have already explained(what chris mentioned) about this and everything, they need the format for the application(they are developing) schema owner as permanent format, which makes their life easier to deal with.

Is there any possibility to set MMDDYYYY format date permanently for a particular user as a permanent
format. It doesn't makes sense to me ask this Q, but Just wondering.. about alternatives relevant for the context we are talking about.

I brought this up because this is exactly what we did on my last project. Actually, this plus changing the NLS_DATE_FORMAT setting in each developer's registry.

Most of the tools look in the registry for the format, so when they were using TOAD or SQL-Station or the like to develop queries, it looked to them as if the format was what they desired - *no extra work* on their part. Then, inside the app, immediately after connecting, they did an ALTER SESSION to change the format for the application's connection, so the application would work correctly. This was done because the DBA staff at that location also balked at changing the default NLS_DATE_FORMAT setting. These 2 minor changes allowed the developers to work with a more user-friendly format without the DBA's taking any un-necessary risk.

So again, I offer that as a valid alternative so that you don't *have* to change the default format.

The epilogue to my little story is that once I got my hands around all the SQL for the application, I made them change their ways anyway. All date values passed between the Business and Data tiers were done through actual date variables, not strings. The formatting of dates is an issue between the UI and Business tiers and should have nothing to do with the database. The SQL should certainly not be written based on an assumed date format. Everything interior to the database abstraction layer should be done strictly through date variables or with format strings when strings variables cannot be avoided.

But that's just my anal take on things :).

As for the implications if you actually do change the format, well that would depend on whether any other applications and/or databases that access your database only use dates as dates, or always convert using a format string, or roll the dice as your developers seem to want to do. Assuming these other applications and/or databases were probably written according to the same standards that the current developers are using, I would assume that changing the format would, indeed, introduce some appreciable risk and should be avoided.

My .02

- Chris

Thanks chris, atleast you agree with me though developers got pissed off with me.

Thats what I have been trying to explain 'em saying there is inherent risk of changing that, as we can't go back and see all the source of applications/databases that are talking to this database.

All I could do is take this up to oracle and see what they are about to say. Thats too for our application developers satisfaction. Thanks anyway.

Our jobs would be great if it wern't for those pesky developers and those bothersome users...

Hi Reddy:

<< format. >>>


Did u try the possibility of having a LOGON trigger which sets the SESSION's NLS_DATE_FORMAT to whatever the user wants?

I didn't try! cuz, that not goin to solve the issue. Developers wanted that schema owner date format to be always MMDDYYYY instead of MMDDYY, No matter who access the schema(application).

That way, they don't need to modify their source and add to_char/to_date builtin functions to format the date.

In other words I wouldn't mind to say they are lazy to do the same too... If they have to they will do... but, they are trying to avoid those changes.

When you talk about NLS_DATE_FORMAT, always keep oracle version in mind and check nls parameters because your default may not necessarily be YY for the year...
Example, in 8i RR for the year is default which introduces it's limitations of 0-49 year belongs to 21th century but 50-99 year still belongs to the 20th century. I think you guys already know about this limitation :-)
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';
VALUE
----------------------------------------
DD-MON-RR
SQL> desc junk;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 DATE
SQL> insert into junk values ('12-NOV-68');
1 row created.
SQL> alter session set nls_date_format='DD-MON-YYYY';
Session altered.
SQL> select * from junk;
COL1
-----------
12-NOV-1968

..
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';
VALUE
----------------------------------------
DD-MON-RR
SQL> insert into junk values ('12-NOV-34');
1 row created.
SQL> alter session set nls_date_format='DD-MON-YYYY';
Session altered.

SQL> select * from junk;
COL1
-----------
12-NOV-1968
12-NOV-2034

It's just a thing to keep in mind together with many other YYYY bugs :-)

Iam aware of this.Thanks anyway...

i want to change the date format
i have changes the NLS_DATE_FOrmat parameter in initX.ora file and i restarted database but change didnot take place.
how to set this ?
thanks in advance

Make sure. It depends on NLS_TERRITORY parm also. Did you bounce the database ? What format did you changed to ? Could you post the results you get for the following Query ?

svrmgr> show parameter NLS_DATE_FORMAT



[Edited by sreddy on 01-22-2001 at 09:45 AM]