NHibernate select query from date field based on Minute

Question

I have a table with a datetime field CreatedOn. I would like to fetch all records where the minute of the datetime field (CreatedOn) is between 31 and 59. I would like to get the NHibernate equivalent of

SELECT * FROM dbo.CL_M_Organization
WHERE DATEPART(mi,CreatedOn) BETWEEN 31 AND 59

The basic NHibernate query is

var result = NHSession.QueryOver<Organization>()
.Where(x=>x.CreatedOn < currentdatetime)
.List<Organization>()
.ToList();

The field CreatedOn is of type datetime.

Anyone please help...

Answer 1

If you're using an older version of NHibernate, you can use the MinutePart extension method (found in the NHibernate.Criterion namespace), along with the IsBetween extension method:

var result = NHSession.QueryOver<Organization>()
    .Where(x => x.CreatedOn.MinutePart().IsBetween(31).And(59))
    .List<Organization>()
    .ToList();

However, if you're using a newer version of NHibernate (>= 4), you should just use the DateTime.Minute property directly. NHibernate will understand what you're trying to do and translate it into SQL properly:

var result = NHSession.QueryOver<Organization>()
    .Where(x => x.CreatedOn.Minute.IsBetween(31).And(59))
    .List<Organization>()
    .ToList();

Both of these will generate the following SQL:

SELECT
    /* All OrganizationUnit columns */
FROM
    Temp this_ 
WHERE
    datepart(minute, this_.CreatedOn) between 31 and 59;