Hi,
I have tried to trunc date values in DB in a situation where user input date is matched against. But I am not able to do that. In my scenario I have a parent and a child and I have to match the date against the child object not the parent object. So I have created an alias to retrieve the collection of children and I try to match the date of child with DB.

Without truncating, everything works fine. But how do I trunc the values, coz it's a requirement. Is there any limitation in Hibernate Criteria API to use 'trunc'?.

Code segment is attached herewith.

Parent to Child has one-to-many relationship (Dog and Puppy)

Dog --> id(Long), name(String), puppies(List<Puppy>) //Note that I deliberately put a new class called Puppy to cater my scenario.
Puppy --> id(Long), name (String), bDate(Date)

<Code_Start>
//------------------------------------
final String puppyList = "puppyList";
criteria.createAlias("puppies", puppyList);

if (searchDTO.getBirthDate() != null) {
final String birthDate = "puppyList.bDate";
criteria.add(Restrictions.eq(birthDate, searchDTO.getBirthDate()));
}

//-----------------------------------
<Code_End>

This criteria query runs successfully. But will not return rows having time portion for birth date of puppy (Let's assume that time portion is there in every record).

But the following code segment does not run.
<Code_Start>
//------------------------------------
final String puppyList = "puppyList";
criteria.createAlias("puppies", puppyList);

if (searchDTO.getBirthDate() != null) {
final String birthDate = "puppyList.bDate";
criteria.add(Restrictions.eq("trunc(" + birthDate + ")", searchDTO.getBirthDate()));
}

//-----------------------------------
<Code_End>

Can anyone help me to sort out this issue. Is this a limitation in Hibernate Criteria API?

Regards,
Asiri

You can use SQL restriction in Criteria Restrictions.sqlRestriction("COLUMN >= to_date('18/05/2009','dd/mm/yyyy HH24:MI:SS')