JPA 2.0 Date Comparsin JPA query

Question

I am trying to execute following query, but its throwing error.

SELECT t from Table t where t.status = :status and t.ndate > (t.ndate - 12/24) AND t.ndate < (t.ndate + 12/24)

Error

ERROR: Filter invalid. Cannot compare field ndate of type java.util.Date to value of type long. Numeric comparisons must be between numeric types only

Do we any other way to do this?

consider using `FORMAT` to format your dates as long "ddmmyyyy" for ex — unique_ptr, Aug 05 '15 at 10:09

score 0 · Answer 1 · answered Aug 05 '15 at 08:48

0

(t.ndate - 12/24) returns a numeric value not a date and that can not be compared with the java.util.Date.

answered Aug 05 '15 at 08:48

Jens

67,715
15
98
113

Use a native query with function `Date_add` r you have to do the calculation in java – Jens Aug 05 '15 at 08:57

score 0 · Answer 2 · edited May 23 '17 at 12:13

I suppose you want to query if the t.ndate is between two fixed dates. Then you can calculate the date you want in Java and use these computed date values (in the example, :start and :end) in the query. This way you can use normal comparison and avoid introducing dependencies on a native query language. You better should use the comparison with BETWEEN in this case, as it is more readable:

t.ndate BETWEEN :start AND :end

See also JPQL SELECT between date statement and especially Java: JPQL date function to add a time period to another date.

JPA 2.0 Date Comparsin JPA query

2 Answers2