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

Question

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?

I imagine you need to group by `TRUNC(date_occured)` don't you? i.e. you want a result per day, not per second. — Tony Andrews, Sep 15 '11 at 15:30

Tony Andrews · Accepted Answer · 2011-09-15T16:59:06.547

15

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:

A : date_occured
B : TRUNC(date_occured)
C : TRUNC(CURRENT_DATE - 30)

edited Sep 15 '11 at 16:59

answered Sep 15 '11 at 15:28

Tony Andrews

129,880
21
220
259

I think the trunc() should also be applied to date_occured in order to ignore that time as well. Additionally I would use `trunc(current_date) - 30` instead – Sep 15 '11 at 15:34
I am interested to know if this solves the issue. I always assumed if you trunk a date with no time, it amounts to doing nothing. – northpole Sep 15 '11 at 15:35
@a_horse_with_no_name - I don't think that makes any difference. I would like to know if I am wrong on that with any references you have. Thanks. – northpole Sep 15 '11 at 15:37
@northopole: no, you are right it does not make a difference. I thought about a a `<=` condition where `trunc`ing date_occurred would have made a difference. – Sep 15 '11 at 15:48
Ya, I am surprised this made any difference. Is the assumption here that the right side trunk will trunk both dates? @Tony, can you provide an explanation? – northpole Sep 15 '11 at 15:52
@northpole: It only seems to logically solve the my problem. Is there a way if I really have to do this `TRUNC(date_occured) >= TRUNC(CURRENT_DATE - 30)`? – Bhesh Gurung Sep 15 '11 at 15:56
@BheshG, that is exactly why I would like an explanation from Tony. Because if date_occured has a time, how would this then resolve? – northpole Sep 15 '11 at 16:04
1

@northpole: explanation added – Tony Andrews Sep 15 '11 at 16:59

score 3 · Answer 2 · answered Sep 15 '11 at 15:33

3

I think you'll want to trunc in the select part too:

 SELECT TRUNC(date_occured) AS short_date_occured, COUNT(*)
 FROM problem 
 WHERE date_occured >= trunc(SYSDATE- 30) 
 GROUP BY short_date_occured;

answered Sep 15 '11 at 15:33

Lost in Alabama

1,653
10
16

score 1 · Answer 3 · answered Sep 15 '11 at 15:32

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 Sep 15 '11 at 15:32

northpole

10,244
7
35
58

score 1 · Answer 4 · answered Sep 15 '11 at 15:45

I would tend to suspect that Tony is correct and that you really only want to TRUNC the right-hand side of the expression.

If you do want to TRUNC both sides of the expression and you're encountering performance issues, you probably need a function based index

CREATE INDEX idx_problem_trunc_dt_occured
    ON problem( trunc( date_occurred ) );

That would allow your original query to use the function-based index.

score 0 · Answer 5 · answered Dec 13 '18 at 17:18

Here is the index friendly approach.

you don't need to use functions on columns if you use Oracle's Native MONTHS_BETWEEN function.

It returns difference in number of months. Days are also given as difference but on the precision side that is why I preferred to use BETWEEN clause

WHERE MONTHS_BETWEEN(date_occured, CURRENT_DATE - 30) BETWEEN 0 AND 1

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

5 Answers5

Linked