Be aware of the difference between CURRENT_DATE and SYSDATE

I was running some queries in SQL Developer against the WebCenter Content (WCC) schema that included date fields such as dInDate. I was comparing the dates against CURRENT_DATE. I was not getting the expected results. I did some googlng and didn’t find a solution, but I did run across a reference to SYSDATE. I tried SYSDATE in my queries and got the expected results.

I did a TO_CHAR on the two date fields and found they returned different times. CURRENT_DATE returned the time from my laptop which was  in the EDT time zone. SYSDATE returned the time from the database server which happened to be in the PDT time zone. I guess if both the database server and my laptop were in the same time zone I would not have seen any problem.

Here is the query I ran to display the two fields.

select to_char(current_date,'DD-MON-YY HH:MI:SS'), to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;

As you can see from the screen shot from SQL Developer they definitely returned different times.


I’m sure there is some command or setting you can use to prevent this problem, but for me the take away is to use SYSDATE in your queries when you want to do any date comparison.

Comments:

Hi, Kevin,

Your observations reflect what's said in the manual about those functions:

http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions019.htm
http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions080.htm

You can alter the return of CURRENT_DATE by changing your session's timezone, as shown below. In the examples below the database server is in Canada and my SQL*Plus is running from Australia. Note that since the db timezone is set as a GMT offset, setting the session timezone to dbtimezone doesn't allow for the DST adjustment.

Hope this helps.

Best regards,
Andre

SQL> alter session set time_zone = local;

Session altered.

SQL> select current_date, sysdate from dual;

CURRENT_DATE SYSDATE
------------------- -------------------
2012-09-21 17:44:12 2012-09-21 03:44:12

1 row selected.

SQL> alter session set time_zone = dbtimezone;

Session altered.

SQL> select current_date, sysdate from dual;

CURRENT_DATE SYSDATE
------------------- -------------------
2012-09-21 02:44:30 2012-09-21 03:44:30

1 row selected.

SQL> select dbtimezone from dual;

DBTIME
------
-05:00

1 row selected.

SQL> alter session set time_zone = 'Canada/Eastern';

Session altered.

SQL> select current_date, sysdate from dual;

CURRENT_DATE SYSDATE
------------------- -------------------
2012-09-21 03:46:43 2012-09-21 03:46:43

1 row selected.

Posted by Andre Araujo on September 21, 2012 at 02:59 AM CDT #

Quite interesting. I have always used SYSDATE since it served all my purposes and quite frankly CURRENT_DATE was not something which I was fully aware of. One scenario where knowing less was good :)

Posted by guest on September 21, 2012 at 06:34 AM CDT #

Thanks for the great info Andre! I knew there was an explanation for it.

Posted by Kevin Smith on September 21, 2012 at 06:57 AM CDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kevin Smith is a Technical Director in Oracle Consulting's WebCenter practice. He has been working with content management products since 2004 when he joined Stellent.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today