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

Why cant you just remove 21 months to the parameter EndDate instead? — jishi, Mar 19 '10 at 20:29
agree with jishi - that leaves just one subtraction instead of hundreds/thousands — increddibelly, Jun 27 '12 at 14:44

score 3 · Answer 1 · edited Feb 19 '21 at 16:25

You can inherit from the dialect you use and register the function.

For example, for MS SQL you can register the dateadd function like this:

public class MyExtendedMsSql2012Dialect : MsSql2012Dialect
{
    protected override void RegisterFunctions()
    {
        base.RegisterFunctions();

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

Then if you configure that dialect you'll be able to use it in HQL and with the Criteria API.

NHibernate HQL date functions

1 Answers1