NLS Date Format in INIT<SIS>.ORA

Hi,

How do you change the NLS_DATE_FORMAT in Initialization parameter file. I know that I can do it in my session either Alter session or Have it in Login File.

But How do you do that in Init.Ora file, it always gives me error I have tried putting in Single quotes and Double quotes but no Help.

This is what I want about the date format ... Oracle 8.1.6/Win2000

Day Month DDth YYYY HH:MI:SS AM

This is the format that I want for my database... Please assist. Thanks

The Correct syntax is
nls_date_format="YYYY-MM-DD"

just addan entry in your init.ora

NLS_DATE_FORMAT = DD/MM/YY HH24:MI:SS

bounce the database

if you want to use this date format in your session you must not set NLS_LANG in your env variables

Hi pando,

Thanks for Reply.

Can't I have this format: Day Month DDth YYYY HH:MI:SS AM
and What others I have to do to be seen in the Database.
You said not to have NLS_Lang in Env Variable, How does this affect the Date Format.

Thanks.

well I dont know how you can show AM and PM in Oracle

if you set NLS_LANG in your client then the client will use the default date format for the NLS_LANG you set

Let´s say you set
NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1
in you winnt registry or enviroment variable in unix (depend where is the client located)

instead of receiving

DD/MM/YY HH24:MI:SS

as date format

you will get

DD/MM/YY

if you use AMERICAN_AMERICA:WE8ISO8859P1

you get

DD-MON-YY

If you dont set any

you get

DD/MM/YY HH24:MI:SS

Let´s see it this way, the parameters you set in client predominates the one from the server, the client side parameters has precedence (I think this is in English not sure the correct word :D)