I want to know how SQL Extensions can be used with Hibernate. I need to use "year" and "month" functions. My HQL is like this:

select {fn YEAR(e.date)}, {fn MONTH(e.date)} from MyObject as e where not e.date is null

I've seen in previous forum messages that this should be correct, but it doesn't work:

net.sf.hibernate.QueryException: undefined alias: {fn

Should I use a SQL based query instead of HQL?. Is there any other way to obtain the month and year of a date field?.

If you're using a very recent version of hibernate you can create a custom dialect and add these functions to the permissable list so hibernate will just pass them straight through to the db. Then just write HQL with the functions in ( think this only works for the select clause ).

and if that doesn't work try to use createSQLQuery (which unfortunately at this moment only allows you to fetch mapped objects and not scalar/projected values)