Home » RDBMS Server » Server Utilities » NLS_DATE_FORMAT in init.ora
NLS_DATE_FORMAT in init.ora [message #69194] Tue, 20 November 2001 01:26 Go to next message
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

----------------------------------------------------------------------
Re: NLS_DATE_FORMAT in init.ora [message #69250 is a reply to message #69194] Thu, 29 November 2001 02:22 Go to previous messageGo to next message
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

----------------------------------------------------------------------
Re: NLS_DATE_FORMAT in init.ora [message #69292 is a reply to message #69194] Tue, 04 December 2001 22:33 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: NLS_DATE_FORMAT in init.ora [message #69301 is a reply to message #69194] Wed, 05 December 2001 21:23 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: NLS_DATE_FORMAT in init.ora [message #70900 is a reply to message #69250] Fri, 09 August 2002 08:18 Go to previous messageGo to next message
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.
icon6.gif  Re: NLS_DATE_FORMAT in init.ora [message #619405 is a reply to message #70900] Tue, 22 July 2014 04:45 Go to previous message
zoqyshairy
Messages: 1
Registered: July 2014
Location: faisalabad
Junior Member

Thanks
Previous Topic: ORA_06512
Next Topic: Sql Loader
Goto Forum:
  


Current Time: Wed Jul 17 23:42:11 CDT 2019