CreateCriteria and MONTH

Question

Hello!

Is it possible to use MONTH in a CreateCriteria-statement?

Does NHibernate support YEAR and/or MONTH?

I have a sql-statement like select obs2.Lopnr from Obs obs2 where MONTH(obs2.Datum)=11)

Best Regards from

Mats

Answer 1

I don't believe it's possible in a criteria statement. The date functions (year, month, day) are supported in HQL queries so they are usable in that way.

Answer 2

ICriteria supports arbitrary SQL as a restriction. Therefore you could do:

var criteria = session.CreateCriteria(typeof(Obs))
    .Add(Expression.Sql("MONTH({alias}.Datum) = ?", 11, NHibernateUtil.Int32);
var results = criteria.List<Obs>();

This will execute a SQL query with {alias} replaced by the alias that NHibernate is using for the Obs table. Of course, this limits your portablility to other databases, as SQL is now embedded in your query.

Another thing to remember is that the names you're using here are the mapped property and class names, not the underlying column and table names.