Hello,

I am trying to select a list of unique dates from a table, and I want those dates rounded to a day.
Example:
2004
2003
....

I am trying to use the following HQL:



This is run against a PostgreSQL database, which provides date_part() function.
The above query does not work in Hibernate, though, because of bad syntax:

Caused by: net.sf.hibernate.QueryException: undefined alias: {fn select {fn date_part(........

I thought, after reading/searching this forum, that {fn ....} would do it.
How do I run queries that use database functions from Hibernate?
I found a few posts that mentioned {fn ...} syntax, but this does not seem to work for me (Hibernate 2.1 final).
I also saw Christian's post from October 2003 that says this is in the docs, but I haven't been able to find it.

Can you see something wrong with my query?

Thank you,
Otis

You can use custom database functions everywhere in your HQL except in the select part by just using them as you would in SQL. Don't know about the {fn} stuff though. (should not be too difficult to do the year-part in java manually)

The problem is that I cannot use 'order by XXX' if I don't have XXX in the select part.

I have tried working around that by using Session's createSQLQuery method, like this:



The problem here is the 'Calendar.class' part.
According to the Javadocs, this has to be a persistable class.
Using java.util.Calendar class there (or any other regular Java class) results in:

Caused by: net.sf.hibernate.MappingException: No persister for: java.util.Calendar


However, I don't want that. I just want a Collection of 'YYYY' Strings back. :)

Is there any way to do this with Hibernate?

Thanks,
Otis