RE: ALTER SESSION SET NLS_DATE

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 15 Mar 2001 12:28:09 -0800
Message-ID: <F001.002CDFD7.20010315112114@fatcity.com>

>-----Original Message-----
>From: Wendy Y [mailto:lannyue_at_yahoo.com]
>
>Can anyone explain to me what exactly 'Alter Session' will do?
>Will it alter the session all the way until it close?
>How do I find out what kind of mode is my SQLPLUS session currently on?
>I have following script like:
>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD-HH24.MI.SS';
>SELECT ...
>FROM ...
>WHERE ...
>     (TO_DATE(SUBSTR(KOMP.DATCRE,1,19),'YYYY-MM-DD-HH24.MI.SS') <
>     TO_DATE(SUBSTR>(KOMP.DATCHG,1,19),'YYYY-MM-DD-HH24.MI.SS')) and
>     (TO_DATE(TO_CHAR(SYSDATE-1),'YYYY-MM-DD-HH24.MI.SS') <
>     TO_DATE(SUBSTR(KOMP.DATCHG,1,19),'YYYY-MM-DD-HH24.MI.SS'));
>When I run the script as above, it will only return 2 records, which is correct.
>However, when I remove "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD-HH24.MI.SS';"
>the query will return 8000 records which is wrong.
>What function does 'ALTER SESSION' have here?

The alter session command will set the default date format. See examples below. You can see what your current session format is with the following query: select * from nls_session_parameters ;

SQL> alter session set nls_date_format = 'YYYYMMDD' ; Session altered.
SQL> select sysdate from dual ;
SYSDATE

20010315
SQL> alter session set nls_date_format = 'DD/MM/YYYY' ; Session altered.
SQL> select sysdate from dual ;
SYSDATE

15/03/2001

When you compare two dates, you don't need to use a to_date function on a field that is already a date. e.g. I could say
select *
from ...
where date_field < sysdate ;
or
select *
from ...
where date_field < to_date ('20010619', 'YYYYMMDD') ;

In your example above, I am guessing that the difference in the rows returned comes from this expression: "TO_DATE(TO_CHAR(SYSDATE-1),'YYYY-MM-DD-HH24.MI.SS')" since you are using a to_date conversion on sysdate (unnecessarily) and the to_date conversion without a format depends on the nls_date_format, as seen in the example below.

SQL> alter session set nls_date_format = 'YYMMDD' ; Session altered.
SQL> select to_char (to_date ('010203'), 'YYYY/MM/DD') from dual ; TO_CHAR(TO

2001/02/03
SQL> alter session set nls_date_format = 'DDMMYY' ; Session altered.
SQL> select to_char (to_date ('010203'), 'YYYY/MM/DD') from dual ; TO_CHAR(TO

2003/02/01

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Thu Mar 15 2001 - 14:28:09 CST

RE: ALTER SESSION SET NLS_DATE_FORMAT