Re: Oracle Newbie's Question

ss wrote:

> I am new to Oracle and confused by the difference between current_date and
> sysdate (well, the SQL Reference doesnt explain their difference at all)!!
>
> My installation is Oracle 9i R2 and when the "dbtimezone" and
> "sessiontimezone" are the same, the "current_date" and "sysdate" returns
> different values (test1 below)?? I have to change the "sessiontimezone" to
> +8:0 in order to bring the two values the same(test2 below). I know
> "current_date" should depends on my current session time zone. But what
> about "sysdate"? Isn't it relative to "dbtimezone"? If not, what else is
> it relative to?
> =============== test 1=============
> SQL> ALTER SESSION SET TIME_ZONE='-7:0';
>
> Session altered.
>
> SQL> select dbtimezone,sessiontimezone,current_date,sysdate from dual;
>
> DBTIMEZONE SESSIONTIMEZONE CURRENT_DATE SYSDATE
> ------------ -------------------- -------------------- --------------------
> -07:00 -07:00 28-JAN-2005 06:31:24 28-JAN-2005 21:31:24
>
> =============== test 2=============
> SQL> ALTER SESSION SET TIME_ZONE='+8:0';
>
> Session altered.
>
> SQL> select dbtimezone,sessiontimezone,current_date,sysdate from dual;
>
> DBTIMEZONE SESSIONTIMEZONE CURRENT_DATE SYSDATE
> ------------ -------------------- -------------------- --------------------
> -07:00 +08:00 28-JAN-2005 21:31:25 28-JAN-2005 21:31:24
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions032.htm#SQLRF00628 CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions157.htm#sthref1845 SYSDATE returns the current date and time set for the operating system on which the database resides

The first is the the session the second the operating system of the server: They can be different.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)