nls_date_format problem

[jgroove]

I am having a probelm with what the default session nls_date format is. I am workign on deveolping applications which access my 7.3.4 oracle database. I have an application that deals with lots of dates. The problem is that I develop on a wintel box and our brand new production server is a unix. I think I have an idea why the applications fail when they are moved to the production server is that their default session parameters are different.

on my wintel box:

select nls_date_format from nls_session_parameters;

parameter value
-------------------- --------------------
nls_date_format dd-mon-rr


on the unix box

select nls_date_format from nls_session_parameters;

parameter value
-------------------- --------------------
nls_date_format mm/dd/yyyy


how can I change the defalut value for one of these? where is the information stored for the session parameters?

 

[jgaines1]

you can set the default system wide format in the init.ora file nls_date_format= mm/dd/yyyy. I belive you can also set this in the session profile too, glogin.sql, the format is a little different.

 

[naushi]

I will answer this by asking a question. The question is 'can we change the nls date format once the database is created and data entered. would it go and change the date format of all the existing data?

Also what does nls stand for? Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[jgroove]

if I am not mistaken, the init.ora file only sets the nls_database_parameters and nls_instance_paramters, but not the nls_session_parameters. if the nls_session_parameters was set on the server, why would I get two different responses from two different machines??

i tried altering the glogin.sql file before, but it didn't seem to work. I think this is because it is the application server(coldfusion) that connects to oracle through native drivers and not through an sql*plus session.

am I way off base? can someone maybe explain why this might be happening?

 

[stefanhei]

Hi.
If you just want to change the NLS_DATE_FORMAT for the current session try:
ALTER SESSION SET NLS_DATE_FORMAT = 'mm/dd/yyyy'

Stefan

 

[sem]

In fact Oracle uses its internal format for storing dates so all these parameters are used ONLY FOR DATA REPRESENTATION. So you may change nls_date_format both by setting it globally (in init.ora) or by changing it on the session level by ALTER SESSION or by using explicit format masks in to_char conversions. If I'm not mistaken NLS stands for National Language Settings