CODEWITHSUNDEEP
nhibernatehql
MegaByte
MegaByte

Reputation: 6845

NHibernate HQL date functions

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

Upvotes: 2

Views: 1944

Answers (1)

Fabio Maulo
Fabio Maulo

Reputation: 466

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.

Upvotes: 3

Related Questions