How to query a date in HQL (Hibernate) with Joda Time?
- by fabien7474
I am sure that someone familiar with HQL (I am myself a newbie) can easily answer this question.
In my Grails application, I have the following domain class.
class Book {
org.joda.time.DateTime releaseDate //I use the PersistentDateTime for persisting via Hibernate (that use a DATETIME type for MySQL DB)
}
In my HQL query, I want to retrieve books whose release date is included in range date1..date2
For instance I tried:
DateTime date1, date2
...
def queryStr = "select * from Book as b where b.releaseDate > $date1 and b.releaseDate < $date2"
def res = Book.executeQuery(queryStr)
But I got the exception ...caused by: org.springframework.orm.hibernate3.HibernateQueryException: unexpected token:
The error token points to date format (for instance 2009-11-27T21:57:18.010+01:00 or Fri Nov 27 22:01:20 CET 2009)
I have also tried to convert date1 into a Date class without success
So what is the correct HQL code ? Should I convert to a specific format (which one?) using the patternForStyle method or is there another -cleaner- way to do it?
Thanks,
Fabien.
