How do I use the current date in an HQL query with an Oracle database?
I'm trying to write this query using Hibernate 3 and Oracle 10.
from Alert alert
where alert.expiration > current_date()
order by alert.priority, alert.updated, alert.name
It's creating SQL like this -
Hibernate: select alert0_.ANNOUNCEMENTS_ID as ANNOUNCE1_1_, alert0_.ANNOUNCEMENT
S_NAME as ANNOUNCE2_1_, alert0_.ANNOUNCEMENTS_PRIORITY as ANNOUNCE3_1_, alert0_.
ANNOUNCEMENTS_EXPIRATION as ANNOUNCE4_1_, alert0_.ANNOUNCEMENTS_UPDATE_DATE as A
NNOUNCE5_1_ from NYC311_ANNOUNCEMENTS alert0_ where (alert0_.ANNOUNCEMENTS_EXPIR
ATION>current_date()) order by alert0_.ANNOUNCEMENTS_PRIORITY , alert0_.ANNOUNC
EMENTS_UPDATE_DATE , alert0_.ANNOUNCEMENTS_NAME
I'm getting all of these wacky errors like "missing right parenthesis" when there is apparently perfectly balanced parenthesis.
Why is Oracle freaking out at this? Is there a better way to write my HQL query?
Answer
Shouldn't it be current_date?
Hibernate will translate it to the proper dialect.
I did not find a real "Hibernate will translate this to that" reference documentation, but the expression, in general, can be found in HQL Expressions for Hibernate 4.3.
Then there is the Java Persistence API 2.0 (JPA) specification which defines expressions for the Java Persistence query language (JPQL) and their meaning e.g. for current_date:
4.6.17.2.3 Datetime Functions functions_returning_datetime:= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP The datetime functions return the value of current date, time, and timestamp on the database server.