Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

writing a HQL query with date calculation

 
Vijay Chauhan
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic