NLS setup

[hokky]

Hi guys,

I just want to setup my date format to look like this :
dd-mm-yyyy

create table hokky (a date);

Table created.

insert into hokky values (sysdate);

1 row created.

select * from hokky;

A
---------
[b]31-JAN-08[/b] --> [red]which I dont want this, I'm expecting 31-01-2008[/red]

I remember I need to set up in my profile by NLS_LANG, something like this below (but I tried doesn't work) :

set NLS_LANG=american_america.US8PC437
set DATE_STAMP='dd-mm-yyyy'

I used to got it worked but I can't remember in which part,
I believe you guys can nail it.

Thanks guys

 

[SantaMufasa]

Hokky,

You can modify your init parameter to read:

nls_date_format='dd-mm-yyyy'

You can test a variety of date formats without modifying your init parameter if you use this code:

SQL> select sysdate from dual;

SYSDATE
---------
01-FEB-08

SQL> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
01-02-2008

Let us know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hokky]

Thanks mufasa,

But I select the date format pretty often, so I don't want everytime I select the date I need to alter the session first. So that's why, I need to do something in my profile which is setup the NLS.

I used to able to figure it out once, but my profile got overwritten by someone and it's gone

If you guys can help me to figure out, that would be appreciated,

Thanks,

 

[sem]

Did you try to set NLS_DATE_FORMAT environment (registry) variable? If you're on Windows then add HKLM/SOFTWARE/ORACLE/NLS_DATE_FORMAT string value.

Regards, Dima

http://seminihin.narod.ru

 

[jimirvine]

Edit your login.sql or glogin.sql file. You'll find it in the sqlplus/admin directory.
Add the alter session command (and any other commands that you want executed on login)

HTH

 

[hokky]

Hi,

sorry I just got back from holiday.

sem,

I'm in Linux environment and I'm trying to set in my profile the NLS_DATE_FORMAT to dd-mm-yyyy but it doesn't work because I need to set the NLS_LANG as well...

I just need the parameter
NLS_LANG=... -> please someone tell me

thanks guys

 

[sem]

AFAIK specific NLS_DATE_FORMAT overrides NLS_LANG settings. Of course, if you have explicit ALTER SESSION in your [g]login.sql script, then the environment variable is ignored. I can't explain why it doesn't work for you, but it works for me (and others):

[oracle@apora oracle]$ export NLS_DATE_FORMAT=DD.MM.YY
[oracle@apora oracle]$ sqlplus scott/tiger

SQL*Plus: Release 8.0.6.0.0 - Production on ??? ??? 12 10:54:40 2008

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.6.0 - Production

SQL> select sysdate from dual;

SYSDATE
--------
12.02.08

Note that my NLS_LANG is RUSSIAN_CIS.CL8MSWIN1251 (you may see that strange characters after Production on)

Regards, Dima

http://seminihin.narod.ru