writing a HQL query with date calculation

[Vijay Chauhan]

The mySQL query below is what I want to write in HQL to obtain a list of all objects with the respective id's.
The fields in the query;
last_update_date is a date &
interval is an integer specifying the number of days.

select acct_id from profiles where sysdate() >= adddate(last_update_date, interval)

Thanks in advance for any help in figuring this out.

[Vijay Chauhan]

I figured it out!
Didnt know i could just use mySQL functions directly in the HQL statement.
Here is the solution for anyone who might be interested;
List result = session.createQuery("from Profile as profile where sysdate() >= adddate(profile.lastUpdateDate, profile.interval ) )").list();

Originally posted by Vijay Chauhan:
The mySQL query below is what I want to write in HQL to obtain a list of all objects with the respective id's.
The fields in the query;
last_update_date is a date &
interval is an integer specifying the number of days.

select acct_id from profiles where sysdate() >= adddate(last_update_date, interval)

Thanks in advance for any help in figuring this out.

[ December 06, 2006: Message edited by: Vijay Chauhan ]

[Mark Spritzler]

Yes, the cool thing about HQL, is that if Hibernate doesn't understand a particular syntax, say a call to sysdate() or your adddate function that is in SQL, then it just passes the text to the database. Meaning if the database suports those functions it will work. So it means that your HQL query doesn't have to always have just HQL, but some SQL can work, but remember when referring to Objects and Attributes you need those object.attribute referencing.

Mark

[Vijay Chauhan]

Thanks for that explanation Mark! And yes that is really cool about hibernate that instead of breaking, it just attempts to send it to the DB as text.