Why does EJB QL between not work?

[jbosschecker]

Hi!,

could someone please give an example of a properly working query, using the EJB QL with

between java.util.Date() START and java.util.Date() END

.

My problem:
I woul like to use this, to compute the sales of an year like this:

public Collection<Order> getAllOrdersOfYear(String year) {

float totalSales = 0.00F;
String firstDayOfYear = year + "-01-01";
String lastDayOfYear = year + "-12-31";
try {
 //select sum(o.total)
 Query query = manager.createQuery("from Order o where o.orderDate between "
 + ":firstDayOfYear and :lastDayOfYear");
 query.setParameter("first", "'" + firstDayOfYear + "'");
 query.setParameter("last", "'" +lastDayOfYear + "'");

 return query.getResultList()) ;
...

But it doesn't work!

[epbernard]

Hum, I already answered the same question somewhere. Guys try not to crosspost.

[jbosschecker]

Thanks for your response.
I had to answer the question, because nobody answered to it.
Sorry.
I have tried to build date objects with the GregorialCalendar like this:

Calendar c = new GregorianCalendar()
c.set(paramYear, 1, 1);
java.util.Date start = c.getTime();
c.set(paramYear, 12, 31);
java.util.Date end = c.getTime();

and here is my query:

Query q = manager.createQuery("from Order o where o.orderDate netween start and end");
q.setParameter("start", start);
q.setParameter("end", end);
...

but it doesn't work!

[epbernard]

q.setParameter("start", start, TemporalType.DATE);

[martinganserer]

Hello,

I have the same problem. Please take a look at my code:

Calendar startDate = new GregorianCalendar(2005,1,1);
Calendar endDate = new GregorianCalendar(2005,1,1);

// Set range of twelve month
endDate.add(GregorianCalendar.MONTH,12);

System.out.println(endDate.get(Calendar.DATE) + "." + endDate.get(Calendar.MONTH) + "." + endDate.get(Calendar.YEAR));

buf.append("select distinct o from CustomerOrder o join o.lineItems l ");
buf.append("where (l.wantedDeliveryDate >= :paramStartDate ");
buf.append("and l.wantedDeliveryDate < :paramEndDate) ");
buf.append("and o.enteredBy = :paramSalesMan ");

Query query = em.createQuery(buf.toString());
query.setParameter("paramStartDate",startDate,TemporalType.DATE);
query.setParameter("paramEndDate",endDate,TemporalType.DATE);
query.setParameter("paramSalesMan",salesMan);

I can execute the query. But I do not get the proper resultset. What should I do?
I have already tried to use Date instead of Calendar. And I have even tried the between operator. I always get more records than I expect!

Thank you!