OraFAQ Forum: Server Utilities » NLS_DATE

Home » RDBMS Server » Server Utilities » NLS_DATE_FORMAT in init.ora

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NLS_DATE_FORMAT in init.ora [message #69194]

Tue, 20 November 2001 01:26

Pui Hua
Messages: 3
Registered: November 2001

Junior Member

Hi all,

I am currently using Oracle 8.1.6 and I am facing difficulty in my attempt to change the global date format in Oracle.

I had changed the NLS_DATE_FORMAT the init.ora file by appending this statement at the end of the file:

nls_date_format = 'yyyy-mm-yy hh24:mm:ss'

The database service is then restarted so that the changes will take effect. However, I noticed this setting (date format) only take effects when I logged in as system user; when I logged in as other user, I have to use the "alter session .." command to change the date format dynamically.

Can anyone suggest a way to alter date format to "yyyy-mm-dd" format at database level?

I understand that I can use the TO_DATE function in order to avoid such troubles; However, my program need to be able access both SQL & Oracle databases, thus I have to avoid using database-specific syntax such as TO_DATE or GetDate().

Any hints or suggestions are really appreciated !

Thanks & regards,
Pui Hua

----------------------------------------------------------------------

Report message to a moderator

Re: NLS_DATE_FORMAT in init.ora [message #69250 is a reply to message #69194]

Thu, 29 November 2001 02:22

smk
Messages: 5
Registered: November 2001

Junior Member

Hi, I think with change in init.ora it should work. I have not tried it, but it should. Other thing you can do to avoid using alter session is try to edit the login.sql on the client side where you are using sql*plus. I think you can write nls_date_format over there. Let me tell you i have not tried, but you can try it out. Do let me know if it works.

Bye

----------------------------------------------------------------------

Report message to a moderator

Re: NLS_DATE_FORMAT in init.ora [message #69292 is a reply to message #69194]

Tue, 04 December 2001 22:33

Pui Hua
Messages: 3
Registered: November 2001

Junior Member

Hi,

I've tried changing the NLS_DATE_FORMAT in both initORCL.ora and glogin.sql, then shutdown and restart the Oracle database. However, the date format still falls back to the default yy-mon-dd format when I execute a simple get date function from the SQL*Plus.

Have I missed out any steps here ?

Regards.

----------------------------------------------------------------------

Report message to a moderator

Re: NLS_DATE_FORMAT in init.ora [message #69301 is a reply to message #69194]

Wed, 05 December 2001 21:23

Pui Hua
Messages: 3
Registered: November 2001

Junior Member

Hi SMK,

After restarting the machine (with the glogin.sql and initORCL.ora changed), the changes seems to take effect.

Thanks!

Regards.

----------------------------------------------------------------------

Report message to a moderator

Re: NLS_DATE_FORMAT in init.ora [message #70900 is a reply to message #69250]

Fri, 09 August 2002 08:18

Gene
Messages: 6
Registered: April 2002

Junior Member

Hi Piu Hua:

I am having similar problem.
What changes you made to glogin.sql ?
I addedd :
alter session set nls_date_format = 'YYYY-MM-DD HH:MI:SS' .
Then SQLPus cannot connect to database.

Thanks for your help.
Gene.

Report message to a moderator

Re: NLS_DATE_FORMAT in init.ora [message #619405 is a reply to message #70900]

Tue, 22 July 2014 04:45

zoqyshairy
Messages: 1
Registered: July 2014
Location: faisalabad

Junior Member

Thanks

Report message to a moderator

Previous Topic:	ORA_06512
Next Topic:	Sql Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 05:39:03 CDT 2025