Thursday Sep 20, 2012

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.

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
« September 2012 »
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
22
23
24
27
28
29
30
      
Today