BaN3 BaN3 - 2 months ago 26
SQL Question

ORA-00932: inconsistent datatypes: expected DATE got BINARY in Hibernate

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))


startDAte and endDate im getting from ajax Date picker but date is system date that i am getting like this

Date utiDate = new Date();


When i execute my query i get the error

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got BINARY

Answer

Trying to find the correct answer, I found an interesting post here.

If :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.