HQL Query for Date comparison

kuma · **Joined:** Fri Oct 21, 2005 1:00 pm **Posts:** 4

Hi,

I need HQL query to do the following:

Last modified date should be less than 15 days from today.

SQL:
Select * from APPL_DATA App WHERE TO_DAYS(NOW()) - TO_DAYS(LST_MOD_DATE) <= 15

Please let me know

Thanks!!!

Marcos Silva Pereira · **Posted:** Tue May 16, 2006 6:47 pm

Not sure if you can nest functions, but Hibernate 3 provides some useful functions to deal with dates. Your HQL will looks like the following:

Code:

from ApplData data where (day(current_date()) - day(data.myDate)) > 15

Kind Regards

kuma · **Joined:** Fri Oct 21, 2005 1:00 pm **Posts:** 4

Thanks for your response!!

Day function is not working.

for e.g day(05/16/2005) =16, day(04/15/2005) =15

16-15 = 1, this will pull 1 month old data also. I need to pull 15 days old data only.

something like this: from ApplData data where (current_timestamp() - data.myDate) <= 15

Please let me know.

bertrand · **Posted:** Wed May 17, 2006 12:28 pm

First of all, you should make clear whether you want the current date of your database engine or the one of your application server...

If the value of your LST_MOD_DATE field is set by your application at the time Hibernate persist the entity, then you had better to build your query using the current time obtained from your application as well.

Example:

Code:

// compute max mod date
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DAY_OF_YEAR, -15);
Date maxModDate = cal.getTime();

// query database
Query query = session.createQuery("from data where lst_mod_date <= :maxModDate");
query.setParameter("maxModDate", maxModDate");
List result = query.list();

On the other hand, if your LST_MOD_DATE field is set by the database (using trigger or whatever) then it is initialized with the database's time. In this case, you should make use of database date functions in your criteria.
The syntax may be database specific. For this you probably have to check the dialect that applies to your database and/or tell hibernate what functions are available (configuration issue).

Hope this helps...

kuma · **Joined:** Fri Oct 21, 2005 1:00 pm **Posts:** 4

lst_mod_date is a timestamp field. And would like to use current date of the database engine.

Can I use

from data where lst_mod_date <= :cal

bertrand · **Posted:** Wed May 17, 2006 3:51 pm

Yes you could - but :cal wont be set to the current date of the database - you will have to supply a value to the parameter yourself - it will then be the current time of your application...

kuma · **Joined:** Fri Oct 21, 2005 1:00 pm **Posts:** 4

Thank you. It worked