Day of the year in HQL?

Question

Is there a way to extract the day of the year (1-366) from a date in HQL? Is the only option for expanding the dialects I need and register functions explicitly? I see that the HSQL and DB2 dialects have a registered day, but not luck with Oracle or SQL Server ...

As far as I can tell, the only available date function functions are year (), month (), day (), etc., and if I don’t miss something, this is not enough to calculate DOY ...

Answer 1

As far as I can tell, the only solution is to expand existing dialects. This turned out to be pretty painless:

public class SQLServerDialect extends org.hibernate.dialect.SQLServerDialect { public SQLServerDialect() { registerFunction( "dayofyear", new SQLFunctionTemplate( Hibernate.INTEGER, "datepart(dayofyear, ?1)" ) ); } }