Adv. querying for dates

langly · **Joined:** Tue Jun 27, 2006 6:48 am **Posts:** 1

Hi there.

I'll skip right to the problem.
In my database I have a set of UserBeans in my database, all which has a born-date field. From this I want to get all of the users that have their birthday on a given date.

Any clue upon how to do this with HQL or Criterias?

I can easily check their age, but I don't know how to ignore the year when doing HQL or Criterias.

In advance thanks..

Kenneth

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

HQL has the day() and month() functions (see ref docs section 14.9, "Expressions", for a more complete list.

Using criteria, you'd probably have to use sqlRestriction with the datepart() SQL funciton (or whatever equivalent function your DBMS offers).

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

With oracle the following code works for me.

Code:

String dateValue = "28";
String sql = "to_char(trunc( my_date_column ), 'dd') = " + dateValue;
Criteria c = session.createCriteria( MyPersistent.class );
c.add( Restrictions.sqlRestriction( sql ) );
List objects = c.list();