java - JPQL not working with dates -

i'm not able data database using dates in query...

i'm working on web application using spring data jpa , oracle database. using @repositoryrestresource annotation in interface declaring query methods named parameters using @param , @query annotations. today needed add new entity dates. in database both columns type of date , used in query. have other 1 type of timestamp , maybe need use in future. , below java representation of 2 columns only, of course setters , getters, has more fields adding this:

@temporal(temporaltype.timestamp) @column(name = "init_date") private calendar initdate;  @temporal(temporaltype.timestamp) @column(name = "agg_date") private calendar aggdate; 

i created new interface case, same way always:

@repositoryrestresource(collectionresourcerel = "customer", path = "customer") public interface icustomerrepository extends pagingandsortingrepository<customer, long> {      @query("select c customer c c.initdate <= to_date(:currentdate, 'yyyy-mm-dd') , c.aggdate >= to_date(:currentdate, 'yyyy-mm-dd')")     public list<customer> filterbydate(@param("currentdate") @datetimeformat(pattern = "yyyy-mm-dd") calendar currentdate);  } 

and i'm receiving error:

org.springframework.dao.dataintegrityviolationexception: not extract resultset; sql [n/a]; nested exception org.hibernate.exception.dataexception: not extract resultset ora-01858: non-numeric character found numeric expected 

i'm trying data database using http request:

http://localhost/webapp/customer/search/filterbydate?currentdate=2017-07-10 

in sql developer query works fine.

i read somewhere in jpql there no date function, in log can see query , parameter looks this:

select     customer0_.customerid col_0_0_,     customer0_.customername col_0_1_,     customer0_.aggdate col_0_2_,     customer0_.initdate col_0_3_,     customer customer0_             , customer0_.aggdate>=to_date(?, 'yyyy-mm-dd')      , customer0_.initdate<=to_date(?, 'yyyy-mm-dd') 2017-07-25 11:55:22.550 trace 12252 --- [ (self-tuning)'] o.h.type.descriptor.sql.basicbinder      : binding parameter [8] [timestamp] - [java.util.gregoriancalendar[time=1499637600000,arefieldsset=true,areallfieldsset=true,lenient=true,zone=sun.util.calendar.zoneinfo[id="europe/berlin",offset=3600000,dstsavings=3600000,usedaylight=true,transitions=143,lastrule=java.util.simpletimezone[id=europe/berlin,offset=3600000,dstsavings=3600000,usedaylight=true,startyear=0,startmode=2,startmonth=2,startday=-1,startdayofweek=1,starttime=3600000,starttimemode=2,endmode=2,endmonth=9,endday=-1,enddayofweek=1,endtime=3600000,endtimemode=2]],firstdayofweek=1,minimaldaysinfirstweek=1,era=1,year=2017,month=6,week_of_year=28,week_of_month=3,day_of_month=10,day_of_year=191,day_of_week=2,day_of_week_in_month=2,am_pm=0,hour=0,hour_of_day=0,minute=0,second=0,millisecond=0,zone_offset=3600000,dst_offset=3600000]] 2017-07-25 11:55:22.550 trace 12252 --- [ (self-tuning)'] o.h.type.descriptor.sql.basicbinder      : binding parameter [9] [timestamp] - [java.util.gregoriancalendar[time=1499637600000,arefieldsset=true,areallfieldsset=true,lenient=true,zone=sun.util.calendar.zoneinfo[id="europe/berlin",offset=3600000,dstsavings=3600000,usedaylight=true,transitions=143,lastrule=java.util.simpletimezone[id=europe/berlin,offset=3600000,dstsavings=3600000,usedaylight=true,startyear=0,startmode=2,startmonth=2,startday=-1,startdayofweek=1,starttime=3600000,starttimemode=2,endmode=2,endmonth=9,endday=-1,enddayofweek=1,endtime=3600000,endtimemode=2]],firstdayofweek=1,minimaldaysinfirstweek=1,era=1,year=2017,month=6,week_of_year=28,week_of_month=3,day_of_month=10,day_of_year=191,day_of_week=2,day_of_week_in_month=2,am_pm=0,hour=0,hour_of_day=0,minute=0,second=0,millisecond=0,zone_offset=3600000,dst_offset=3600000]] 

and honest, have no idea problem here... format date in database yy/mm/dd, wasn't working me... tell me i'm missing or doing wrong??

edit [answer gusti arya]:

i tried 2 things. before update changed type , left @datetimeformat. had same error calendar type. after removing @datetimeformat, having same after update error:

org.springframework.data.repository.support.querymethodparameterconversionexception: failed convert 2017-07-10 java.util.date! caused by: org.springframework.core.convert.conversionfailedexception: failed convert type [java.lang.string] type [org.springframework.data.repository.query.param java.util.date] value '2017-07-10'; nested exception java.lang.illegalargumentexception 

what more interesting, have second query same, without to_date function , have same error above. had:

persistent entity must not null 

edit 2 [related query in log]:

i noticed query posted here not same see in log... entity contains embeddedid in customerid , customername. , 2 columns appears thrice... here valid log of query:

select     customer0_.customerid col_0_0_,     customer0_.customername col_0_1_,     customer0_.customerid col_1_0_,     customer0_.customername col_1_1_,     customer0_.customerid customerid1_6_,     customer0_.customername customername2_6_,     customer0_.aggdate aggdate3_6_,     customer0_.initdate initdate4_6_,     customer customer0_             , customer0_.aggdate>=to_date(?, 'yyyy-mm-dd')      , customer0_.initdate<=to_date(?, 'yyyy-mm-dd') 

**edit [response brian]: **

and types in entity should calendar, right? put 2 different temporal annotation 2 fields. 1 points temporaltype.timestamp , other temporaltype.date. problem passing calendar value http parameter. tried 4 versions of url:

1. http://localhost/webapp/customer/search/filterbydate?currentdate=2017-07-10 2. http://localhost/webapp/customer/search/filterbydate?currentdate=2017-07-10 13:08:24.000+0000 3. http://localhost/webapp/customer/search/filterbydate?currentdate=2017-07-10t13:08:24.000+0000 4. http://localhost/webapp/customer/search/filterbydate?currentdate=1499692104 

but none of not working...

without further digging detail, think need change

@query("select c customer c c.initdate <= to_date(:currentdate, 'yyyy-mm-dd') , c.aggdate >= to_date(:currentdate, 'yyyy-mm-dd')")

to

@query("select c customer c c.initdate <= :currentdate , c.aggdate >= :currentdate")

why? because fields initdate , aggdate of type java.util.calendar , parameter currentdate. matches , spring data jpa provider binds java.util.calendar sql timestamp can see in log

...binding parameter [8] [timestamp]...