Gabriel Bogéa Perez

I'm changing my blog to use an NHibernate data layer. I'll post about this experience soon.

One thing I think is worth mentioning is how to write a query to filter by date only. Yes, I want to ignore the time part of the date class. I new to NHibernate and I thought that there would be a function to do this. Well there isn't. There is nothing bad about this I just wanted to write about it to let others find the recommended solution to solve this problem.

The way to do it is by using BETWEEN and comparing the hour zero (00:00:00) of the day and the last second of the day (23:59:59).

Here is an example where I need to find all posts published in a given date:

public IList<Post> FindByDate(DateTime datePublished)
{
    DateTime initDate = datePublished.Date;
    DateTime endDate = datePublished.Date.AddDays(1).AddSeconds(-1);
    return Session.CreateCriteria<Post>()
        .Add(Expression.Between("PublishDate", initDate, endDate))
        .List<Post>();
}

 And here is the same example using HQL:

public IList<Post> FindByDate(DateTime datePublished)
{
    DateTime initDate = datePublished.Date;
    DateTime endDate = datePublished.Date.AddDays(1).AddSeconds(-1);
    string hql = "from Post where PublishDate between :initDate and :endDate";
    return Session.CreateQuery(hql)
        .SetDateTime("initDate", initDate)
        .SetDateTime("endDate", endDate)
        .List<Post>();
}

Very simple. My only doubt was if there were any other way to do this. I found a discussion on the NHibernate group where Ayende says that this is the way to do it. Case closed :-)