9

My query is like this:

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

I get startDate and endDate from ajax date picker. date is the system date, which 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
AJPerez
  • 3,435
  • 10
  • 61
  • 91
BaN3
  • 435
  • 1
  • 3
  • 16
  • Just to be sure the problem is there: change `:date` with `:enddate`. You won't have the correct result, but you'll be sure that the problem is in the type of `:date` and not in `:startdate` or `:enddate`. You should obtain a result or a different exception. – eternay May 16 '13 at 09:29
  • A guess would be the nested condition (:endDate is not null or VDDATE between :startDate AND :date). Have you tried running it without the nested brackets? – howiewylie May 16 '13 at 09:30
  • @eternay I replaced it with startdate as u suggested but i still get the same error. – BaN3 May 16 '13 at 11:35
  • @howiewylie: I removed the nested condition and still the same error occurs. – BaN3 May 16 '13 at 11:36
  • So, the problem is not generated by `:date`, since replacing it by another value didn't remove the exception. Can you please give the database you're using (Oracle, MySql, SQLServer...) and the type of DDDATE and VDDATE fields? – eternay May 16 '13 at 12:47
  • 1
    Sorry, the error code (ORA-xxx) shows that it's an Oracle database. Just forget my previous comment. – eternay May 16 '13 at 13:14
  • The query works fine with just `(:startDate is null or :endDate is null or DDATE between :startDate AND :endDate)` but when i add the condition `(:startDate is null or (:endDate is not null or DDATE between :startDate AND :date)` the error occurs – BaN3 May 16 '13 at 14:35
  • This problem occurs only when my endDate is null. I tried the TO_DATE(:startDate,'yyyymmdd') suggested by @eternay but it throws me an error `ORA-01858: a non-numeric character was found where a numeric was expected ` so i converted startDate and endDate to string before assigning and it gives me an error `ORA-01841: (full) year must be between -4713 and +9999, and not be 0` I dont know what this is for – BaN3 May 16 '13 at 14:59

2 Answers2

10

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.

Community
  • 1
  • 1
eternay
  • 3,754
  • 2
  • 29
  • 27
  • Ur answer helped me i did it using stringbuilder like this ` if(startDate!=null) if(endDate!=null) temp.append("AND (TRS_RCVDDATE between :startDate AND :endDate) "); if(startDate!=null) if(endDate==null) temp.append("AND (TRS_RCVDDATE between:startDate AND :date) ");` – BaN3 May 21 '13 at 10:16
2

Properties :startDate and :endDate in your bean are not of type java.util.Date, but something else that Hibernate can't convert to SQL-DATE automatically and is sending BINARY as default.

You need to implement javax.persistence.AttributeConverter< X, Y >

There is some tutorial on that.

Hint: if you place that converter in some jar included in your war app, you must explicitly list it in persistemce.xml, otherwise your converter will not be found:

    <persistence-unit name="default" transaction-type="JTA">
        <jta-data-source>java:/mydb</jta-data-source>

        <!-- register converter -->
        <class>com.example.persistence.LocalDateTimeConverter</class>
        ...
radzimir
  • 1,178
  • 13
  • 10