CODEWITHSUNDEEP
sqloracle-database
Dreamer
Dreamer

Reputation: 7551

Oracle CURRENT_DATE doesn't work in query

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

Answers (2)

Lajos Arpad
Lajos Arpad

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

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

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

Related Questions