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?
CompareTo(DateOnly) Compares the value of this instance to a specified DateOnly value and returns an integer that indicates whether this instance is earlier than, the same as, or later than the specified DateTime value.
Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" using date subtraction to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.
The DATEDIFF() function returns the difference between two dates.
the LIKE operation is not permitted to be used with IN.
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With