OraFAQ Forum: SQL & PL/SQL » nls_date_format

Home » SQL & PL/SQL » SQL & PL/SQL » nls_date_format (oracle 10.2.0.3)

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

nls_date_format [message #468378]

Thu, 29 July 2010 23:01

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I wanted to change session nls_date_format to change display format of the date. I dont want to use to_char. I wanted to change session date display settings. my current display format is 'dd/mm/yyyy'.

I did as below but it is not reflecting the changes.

SQL> select sysdate from dual;
 
SYSDATE
-----------
30/07/2010
 
SQL> alter session set nls_date_format='dd-mon-yy';
 
Session altered
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
30/07/2010
 
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';
 
Session altered
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
30/07/2010
 
SQL> select * from nls_session_parameters;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   ENGLISH
NLS_TERRITORY                  AUSTRALIA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AUSTRALIA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd-mon-yy
NLS_DATE_LANGUAGE              ENGLISH
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT           dd-mon-yy hh24:mi:ss
NLS_TIME_TZ_FORMAT             HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MON/RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected
 
SQL> alter session set nls_timestamp_tz_format='dd-mon-yy hh24:mi:ss';
 
Session altered
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
30/07/2010

Can you please help me in getting the date format in 'dd-mon-yy' or any other format that we give. I dont want to use mask as if i use i need to use at too many places.

Regards,
Pointers

Report message to a moderator

Re: nls_date_format [message #468379 is a reply to message #468378]

Thu, 29 July 2010 23:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It works for me.

SQL> set term on echo on
SQL> set time on
21:03:10 SQL> select sysdate from dual;

SYSDATE
---------
29-JUL-10

21:03:20 SQL> @sql/time.sql
21:03:34 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

21:03:34 SQL> select sysdate from dual;

SYSDATE
-------------------
2010-07-29 21:03:47

Report message to a moderator

Re: nls_date_format [message #468380 is a reply to message #468379]

Thu, 29 July 2010 23:07

pointers
Messages: 451
Registered: May 2008

Senior Member

Thanks for your reply Blackswan.

Yes it worked for me in my personal computer when i tried yesterday.
But in my production database (office), when i try, it is not working.

Regards,
Pointers

Report message to a moderator

Re: nls_date_format [message #468381 is a reply to message #468380]

Thu, 29 July 2010 23:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But in my production database (office), when i try, it is not working.
did you even bother to repeat what I did on production database?
if not, why not?
I showed you what works!

Report message to a moderator

Re: nls_date_format [message #468382 is a reply to message #468381]

Thu, 29 July 2010 23:12

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

SQL> alter session set nls_date_format='dd-mon-yy';

Session altered.

SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';

Session altered.

SQL> select cast (sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)
------------------------
29-jul-10 21:11:03

SQL> select sysdate from dual;

SYSDATE
---------
29-jul-10

By
Vamsi

Report message to a moderator

Re: nls_date_format [message #468383 is a reply to message #468379]

Thu, 29 July 2010 23:13

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

1/ Do not use 'dd-mon-yy hh24:mi:ss'

2/ USE 'YYYY-MM-DD HH24:MI:SS';

With your case :-


SQL> select sysdate from dual;

SYSDATE
---------
30-JUL-10

Elapsed: 00:00:00.56
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';

Session altered.

Elapsed: 00:00:00.28
SQL> select sysdate from dual;

SYSDATE
---------
30-JUL-10

Elapsed: 00:00:00.54

With BlackSwan Case :-

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.28
SQL> select sysdate from dual;

SYSDATE
-------------------
2010-07-30 00:09:28

Elapsed: 00:00:00.56
SQL>

Report message to a moderator

Re: nls_date_format [message #468397 is a reply to message #468383]

Fri, 30 July 2010 00:12

pointers
Messages: 451
Registered: May 2008

Senior Member

Hey thanks all for you replies.

I am not sure why it is not working in my production database.

I checked as Blackswan suggested.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
Session altered
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
30/07/2010
 
SQL>

Is there any settings that are stopping these changes to reflect.

Regards,
Poiners

Report message to a moderator

Re: nls_date_format [message #468398 is a reply to message #468397]

Fri, 30 July 2010 00:19

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I doubt you have got the above from sql session. Did you just copy from above?
nls_date_format is for date.
nls_timestamp_format is for timestamp.
Check my update once again.

By
Vamsi

Report message to a moderator

Re: nls_date_format [message #468426 is a reply to message #468378]

Fri, 30 July 2010 01:31

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Check if there is any database trigger than force the date format.

Regards
Michel

Report message to a moderator

Re: nls_date_format [message #468429 is a reply to message #468426]

Fri, 30 July 2010 01:34

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Michel Cadot wrote on Fri, 30 July 2010 01:31

Check if there is any database trigger than force the date format.

Regards
Michel

Michel I have no Idea of this concept , and me confused.

1/ How ?
2/ Why ?

Anybody would want to do so ?

Report message to a moderator

Re: nls_date_format [message #468436 is a reply to message #468429]

Fri, 30 July 2010 01:48

pointers
Messages: 451
Registered: May 2008

Senior Member

@Vamshi,
I dont want to use any masking like "cast" or "to_char" the reason is that incase i use, i need to use at too many places. So looking for settings (nls_date_format)

@Micheal,
There are no triggers created.

Regards,
Pointers

Report message to a moderator

Re: nls_date_format [message #468448 is a reply to message #468436]

Fri, 30 July 2010 02:05

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Some how it is unbelievable.
Can you get the output from sql*plus again?

alter session set nls_date_format='dd-mon-yy';
select sysdate from dual;
alter session set nls_date_format='dd-mon-yyyy';
select sysdate from dual;
alter session set nls_date_format='dd-mon-yy hh2h:mi:ss';
select sysdate from dual;
alter session set nls_date_format='dd-mon-yyyy hh2h:mi:ss';
select sysdate from dual;

By
Vamsi

Report message to a moderator

Re: nls_date_format [message #468449 is a reply to message #468436]

Fri, 30 July 2010 02:05

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

There are no triggers created.

This is wrong there are many triggers by default, did you check them all?

SQL> select owner, count(*) from dba_triggers group by owner;
OWNER                            COUNT(*)
------------------------------ ----------
MDSYS                                  21
HR                                      2
SYSTEM                                  2
EXFSYS                                  5
SYSMAN                                 48
OE                                      3
XDB                                     2
SYS                                     7
WMSYS                                   2

Regards
Michel

Report message to a moderator

Re: nls_date_format [message #468462 is a reply to message #468449]

Fri, 30 July 2010 02:27

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

@pointers The Example I have posted is from the Same Session. and my DB version is

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.57

Report message to a moderator

Re: nls_date_format [message #468470 is a reply to message #468429]

Fri, 30 July 2010 02:39

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

2/ Why ?

Anybody would want to do so ?

Because some applications (didn't you see many here?) use an implicit conversion on date and don't want users change it.

Quote:

1/ How?

Well I thought it was possible but I already answered and demonstrated in a usenet forum a couple of years ago that it is not possible as ALTER SESSION does not trigger any trigger, unfortunatly.

Regards
Michel

Report message to a moderator

Previous Topic:	to convert character into number
Next Topic:	URGENT: find all data items in a hierarchy - recursion?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 05 16:32:55 CDT 2025