Nhibernate date comparison

Question

Here's the problem I'm trying to solve. I want to pass a date, then retrieve all itemorders that were picked on that date using NHibernate.

When I pass a orderPickDate to the method below, it never comes back with a result. I don't want to pass a date range, I just want to pass a single date, ignore the time, and if any itemOrders exist with that pick date return them.

public IList<ItemOrder> GetItemOrderByCriteria(int? itemNumber, int? warehouseNumber, DateTime? orderPickDate)
    {
        try
        {
            NHibernate.ICriteria criteria = NHibernateSession.CreateCriteria(typeof(Core.ItemOrder));

            if (itemNumber.HasValue)
                criteria.CreateCriteria("Item", "Item").Add(Expression.Eq("Item.ItemNumber", itemNumber.Value));


            if (warehouseNumber.HasValue)
                criteria.CreateCriteria("Warehouse", "Warehouse").Add(Expression.Eq("Warehouse.WarehouseNumber", warehouseNumber));

            if (orderPickDate.HasValue)
                criteria.Add(Expression.Eq("OrdPickDate", orderPickDate));

            return criteria.List<Core.ItemOrder>();
        }
        catch (NHibernate.HibernateException he)
        {
            DataAccessException dae = new DataAccessException("NHibernate Exception", he);
            throw dae;
        }

    }

Here's how this column is set up in the mapping:

<property name="OrdPickDate" column="ORD_PICK_DATE" type="date" not-null="true"/>

When I look at the sql nhibernate creates, it adds the following where clause(I passed it 12/1/2009 12:00:00 AM):

WHERE  this_.ORD_PICK_DATE = '2009-12-01T00:00:00.00'

If I try to run the query in a db editor I get an error saying "ORA-01861: literal does not match format string." Should I be taking a different approach in creating my criteria?

Answer 1

Hi Justin,

the problem you are describing comes from the fact that in Oracle a DATE type is a point in time. It always has the time component, even if sometimes it is not displayed (the time component is hidden).

To perform a date search you would either:

1. compare with a range of date ( WHERE dt BETWEEN :d1 AND :d2 or WHERE dt >= :d1 AND dt <= :d2 )
2. compare the "day" portion of the date (for example WHERE trunc(dt) = :d1 )
3. store only the date portion in your column (i-e for all rows trunc(dt)=dt or in other words all rows are at "12:00AM"), preferably enforced by a column constraint. The WHERE dt = :d1 in that case would work.

In all tree cases you would put a date type at both sides of the operand. I suppose Hibernate naturally uses the right datatype when you specify "DATE". In SQL*Plus, you would explicitely use the right datatype with a to_date function:

WHERE  this_.ORD_PICK_DATE = to_date('2009-12-01 00:00:00', 
                                     'yyyy-mm-dd hh24:mm:ss')

To adress performance issues: case (1) and (3) will be able to use regular indexes on the column whereas case (2) will not.