Reputation: 7551
I have a simple query to filter out the due_date
value is today.
SELECT *
FROM ORDER
WHERE DUE_DATE = CURRENT_DATE AND
ROWNUM <= 10
ORDER BY DUE_DATE ASC
However, even I have a bunch of order has due date as today, the query actually return 0 rows.
How come? Thanks in advance.
Sorry I forget to mention the DUE_DATE is of type Date
Upvotes: 0
Views: 1682
Reputation: 77083
CURRENT_DATE contains the month, the day and the year. Your due date probably contains hour, minute, second too. If so, you should truncate your dates when you are doing the comparison.
Upvotes: 0
Reputation: 50077
You need to either truncate your dates, as in
SELECT *
FROM ORDER
WHERE TRUNC(DUE_DATE) = TRUNC(CURRENT_DATE) AND
ROWNUM <= 10
ORDER BY DUE_DATE ASC
or use a ranged comparison, as in
SELECT *
FROM ORDER
WHERE DUE_DATE BETWEEN TRUNC(CURRENT_DATE)
AND TRUNC(CURRENT_DATE) + INTERVAL '1' DAY - INTERVAL '1' SECOND AND
ROWNUM <= 10
ORDER BY DUE_DATE ASC
The latter example may perform better because an index can be used (assuming you have an index on DUE_DATE). Of course, you could also add a function-based index on TRUNC(DUE_DATE) in which case either would likely perform equally well.
Share and enjoy.
Upvotes: 5