using date functions in hibernate criteria restrictions (non-HQL)

[rafuzo]

Hello all,

I have a DetachedCriteria on which I am trying to add a restriction. Essentially I want the following SQL where clause:

WHERE MONTH(someDate) = <some month integer value>

-- but I can't seem to find the proper syntax for this restriction:

crit.add(Restrictions.eq("month(someDate)", cal.get(GregorianCalendar.MONTH)));

I've seen sqlRestriction which I think I could use to solve this problem, but I'm interested to learn how (if possible) this can be used with the eq restriction.

Thanks

[lloyd.mcclendon]

hmm maybe try

WHERE ? between '1/01/07' AND '2/01/07'


in criteria speak,

    criteria.add(Expression.and(
            Expression.gte(date, dateUtil.firstOfMonth(date)),
            Expression.lt(date, dateUtil.firstOfNextMonth(date)));

I fully encourage you to come up with your own DateUtil bean for operations like this. The calendar API sucks, it is very nice to put all date manipulations in one place. Our dateUtil has become very important and has about 30 or so useful operations.

[karldmoore]

As lloyd said doing a between should do the job, the problem is getting access to the date your actually trying to query on. It's quite an interesting problem though, can't say I've thought of this before.