Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

In order for hibernate to generate "select * from BDSource.MBR_VEW_PRFL a where current date between a.ELIG_EFF_DT and a.ELIG_END_DT", I have following mapping done.

<query name="ByEligibilityDates"><![CDATA[
select currentMemberKey
from CurrentMemberKey as currentMemberKey
where current_date() between eligibilityEffectiveDate and eligibilityEndDate
]]></query>


In the above query, if I need to change the where clause to do "where current date between a.ELIG_EFF_DT and a.ELIG_END_DT - 1 DAY" in DB2, what would be the syntax in HQL that I would need to use for hibernate to generate correct where clause?

I tried a couple of options. DB2 threw me an exception for the first one and Hibernate doesn't understand DAY token incase of 2nd.
1. where current_date() between eligibilityEffectiveDate and eligibilityEndDate - 1
2. where current_date() between eligibilityEffectiveDate and eligibilityEndDate - 1 DAY

If some one could confirm the correct syntax that I would need to use for my mapping, I would appreciate it. Thanks!

Hibernate version: 3.0

[b]Mapping documents:
<query name="ByEligibilityDates"><![CDATA[
select currentMemberKey
from CurrentMemberKey as currentMemberKey
where current_date() between eligibilityEffectiveDate and eligibilityEndDate
]]></query>

Code between sessionFactory.openSession() and session.close():
NA
Full stack trace of any exception that occurs:

Name and version of the database you are using:
DB2 8.1

The generated SQL (show_sql=true):
select * from BDSource.MBR_VEW_PRFL a where current date between a.ELIG_EFF_DT and a.ELIG_END_DT

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?
No

Read this: http://hibernate.org/42.html