Matching Dates without time in Criteria

[Will Shaver]

Here's a question for those who've gone down this road: how best to match dates but not times.

 

Say I've got a Person entity with a HireDate (DateTime) property. I want to match only the date but not the time on a query.

 

Name  HireDate

Joe  1/1/08 1pm

Fred 1/1/08 2pm

Tim 1/2/08 4pm

 

Here's roughly what I want to do:

criteria.Add(Restrictions.Eq("HireDate", DateTime.Parse('1/1/08')));

 

Of course this will return zero results as none of my People have a hire TIME of midnight.

 

I can cheat and do something like:

DateTime date = DateTime.Parse('1/1/08');

criteria.Add(Restrictions.Ge(propertyName, date));
criteria.Add(Restrictions.Lt(propertyName, date.AddDays(1)));

Which will return the correct results (Joe and Fred) but seems pretty lame.

In old school SQL I would use CONVERT on the database field to truncate it to only the date... but this may actually be slower, I have no idea. I also haven't a clue how to do this via Criteria (or even HQL) queries.

Recommendations from the gurus?

 -Will

 

 

[Gustavo Ringel]

using a formula field which will do the SQL cast won't work for the criteria? By the way if there is a need of doing sometimes queries with the time and sometimes not you can add a calculated field to the DB or query on a view which has only the dates...

 

Gustavo.

[Ayende Rahien]

Use between, that is the way to do this.