I want to pass a query like this to Hibernate3 HQL:



I realise that the reference way to do this is to used a named variable in the query and then set it from Java code, but I can't easily do that because the query text is ad-hoc, built from a Velocity template.

I also realise that HQL allows scalar functions that are supported by the underlying DB (Oracle's TO_DATE, for example), but I don't want to rely on that because I don't know what my underlying DB is going to be at runtime.

So, my question is: can a query containing some kind of date literal be expressed in HQL, and it will work regardless of the actual database?

Thanks for help

YYYY-MM-DD is the ANSI standard date format so it *should* work with any databases. Though, as usual, there might be exceptions. Some database, like MySQL, only supports this format. Ok, MySQL allows a lot of variants with and without delimiters, but the year, month, day order is required.

Thanks for the reply.

Does this mean that there is no built-in way to do this kind of date comparison in the HQL, whereby Hibernate uses its knowledge of the underlying dialect to generate a correct query for that DB?

I don't think Hibernate does any date translations. I can't find anything in the Dialect classes that seems to be related to this. In some databases, for example PostgreSQL, the date format is configurable.

Hmm, that seems odd. Why leave out dates when it does dialect-specific things for other data types? For example, I've seen it generate DB-specific versions of HQL queries that concatenate strings using ||. Not that I'm doubting your knowledge - I'd pretty much come to the same conclusion, to be honest - but it just seems odd.

If one of the Hibernate team could confirm this I'd be grateful - just want to avoid re-inventing the wheel...