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?
For this condition you only need to TRUNC the right-hand side:
WHERE date_occured >= TRUNC(CURRENT_DATE - 30)
Why? Because if TRUNC(date_occured) is later than TRUNC(CURRENT_DATE - 30), then any moment in time after TRUNC(date_occured) is bound to be later than TRUNC(CURRENT_DATE - 30) too.
It is obviously always true that date_occured >= TRUNC(date_occured) (think about it).
Logic says that if A >= B and B >= C then it follows that A >= C
Now substitute: