My Query is like this
from TRS where (:startDate is null or :endDate is null or DDATE between :startDate AND :endDate) AND (:startDate is null or (:endDate is not null or DDATE between :startDate AND :date))
Date utiDate = new Date();
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got BINARY
Trying to find the correct answer, I found an interesting post here.
:endDate is null, you can't be sure the condition
DDATE between :startDate AND :endDate won't be evaluated. And if it's evaluated, Oracle will try to convert a null value to a date, so it'll give you an error.
Try to test the query removing the
DDATE between :startDate AND :endDate part: you shouldn't have the error anymore. Then you'll have to modify your query to be sure the
between operator won't be evaluated if
:enddate is null. In this post, they recommend using
CASE statements inside the
WHERE clause. Maybe it can solve your problem.
About short-circuit evaluation and Oracle database, I found that question that can help you understand the problem.