Alex Rivera | Logout

How to compare two DATE values based only on date part in Oracle?

Asked 2011-09-15T15:25:22.597
12

I am trying to get counts for last 30 days with the following query -

SELECT date_occured, COUNT(*) FROM problem
WHERE date_occured >= (CURRENT_DATE - 30)
GROUP BY date_occured;

//date_occured field is of type DATE.

Basically, in my query I am trying to compare only the date part in the condition date_occured >= (CURRENT_DATE - 30), but it seems to compare the time too.

I tried the TRUNC as follows -

TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30)

But when run the query it never returns.

I also tried -

SELECT date_occured, COUNT(*) FROM problem    
GROUP BY date_occured
HAVING TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30);

Again it never returns.

How can I compare only the date parts from two DATE values in Oracle?

Edit
Report

1 Answer

1

Try using SYSDATE vs CURRENT_DATE. Sysdate uses the server's local time where CURRENT_DATE returns current date/time for the server in the client's connection's local time.

answered 2011-09-15T15:32:20.783

Your Answer