Home JPQL Query between dates using Calendar
Reply: 0

JPQL Query between dates using Calendar

user2089
1#
user2089 Published in May 22, 2018, 9:23 pm

I am having troubles making a query to extract all data from a single day.

This are my Calendar objects:

2018-04-13T00:00:00.000Z//Fri Apr 13 23:59:59 CEST 2018
2018-04-15T00:00:00.000Z//Sun Apr 15 00:00:00 CEST 2018

This is my named query:

select count(u) from EntityPoll u where pollId = :pollId and FUNCTION('TRUNC', u.requestDate) > :startDate and FUNCTION('TRUNC', u.requestDate) < :endDate

This is my hibernate generated sql (return 0 elements):

select count(entitypoll0_.ID) as col_0_0_ from NEOL_ES_POLL entitypoll0_ 
where entitypoll0_.POLL_ID=? 
and trunc(entitypoll0_.REQUEST_DATE)>? 
and trunc(entitypoll0_.REQUEST_DATE)<?

This is the same query, but executed in SQL Developer (5 results, ok):

select count(entitypoll0_.ID) as col_0_0_ from NEOL_ES_POLL entitypoll0_
where entitypoll0_.POLL_ID=1500541052868 
and trunc(entitypoll0_.REQUEST_DATE) > TO_DATE('2018-04-13','yyyy-MM-dd') 
and trunc(entitypoll0_.REQUEST_DATE) < TO_DATE('2018-04-15','yyyy-MM-dd');

Any way, this last query have 2 to_Date objects replacing my calendar objects, but I can't find the difference of results with this.

My Dao:

Query q = createNamedQuery(EntityPoll.SELECT_BY_POLLID);
        q.setParameter("pollId", pollId);
        q.setParameter("startDate", startDate, TemporalType.DATE);
        q.setParameter("endDate", endDate, TemporalType.DATE);
        try {
            return ((Long) q.getSingleResult()).intValue();

This my data's table:

2141777690  1500541052868   14/04/18
2141777750  1500541052868   14/04/18
2141777810  1500541052868   14/04/18
2141777870  1500541052868   14/04/18
2141777930  1500541052868   14/04/18
2141778470  1500541052868   13/04/18
2141778590  1500541052868   13/04/18
2141778980  1500541052868   13/04/18
2141779010  1500541052868   13/04/18
2141779100  1500541052868   13/04/18
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.247941 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO