Be aware of the difference between CURRENT_DATE and SYSDATE
- by Kevin Smith
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.
