How do I use the current date in an HQL query with an Oracle database?

Question

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 1

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.

Answer 2

Is current_date() a Hibernate function?

I would use sysdate instead. like this:

where alert.expiration > sysdate  

Or to ignore time of day:

where alert.expiration > trunc(sysdate)