NHibernate HQL date functions

Question

I'm writing a notification platform using C# and NHibernate. I'm having difficulties with my queries.

I have a Customer entity - which contains an AssessmentCompleted Property. A notification should be sent out 21 months after certification. So my query needs to include all customers where their AssessmentCompletedDate + 21months < currentDate. How do I achieve this? Is there a month add method in NHibernate? I need to add 21 months to each AssessmentCompletedProperty. My query needs to look something like:

SELECT new Notification(c.Id, c.Description, c.AssessmentCompleted + 21
            FROM Cusomter c 
            AND c.AssessmentCompleted + 21 <= :EndDate

Answer 1

You can inherit from your dialect and register the properly function. For example, for MS SQL you can register the dateadd function:

RegisterFunction("dateadd", new SQLFunctionTemplate(NHibernateUtil.Date, "dateadd(?1, ?2, ?3)"));

That done you are able to use it in HQL and Criteria.