If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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 :),

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.