Use Linq to SQL to search only temporary parts of a date in SQL 2008

Question

I want to be able to search in the DateTime field not only by date, but also by time range. For example, "Get me all the records in the month of May created between 13:00 and 15:00." SQL I'm looking for to create:

WHERE CreatedOn BETWEEN '05/01/2010' AND '06/01/2010' AND CONVERT(time(4), CreatedOn) BETWEEN '01:00 PM' AND '03:00 PM' 

I can't figure out how to get the lambda expression to get me there. Anyone got it? I have System.Linq.Dynamic in my project. Just as you can pass the string as an OrderBy clause, I thought I could do the same with the Where method as follows:

 results = results.Where("CONVERT(time(4), OccurredOn) BETWEEN '{0}' AND '{1}'", Criteria.OffenseTimeStart, Criteria.OffenseTimeEnd); 

But this throws an exception in System.Linq.Dynamic. Does anyone have pointers?

THANKS FOR THE HELP! Here is my snippet of working code. I set my time criteria properties as TimeSpans:

  if (Criteria.OffenseDateStart.HasValue) results = results.Where(o => o.OccurredOn >= Criteria.OffenseDateStart); if (Criteria.OffenseDateEnd.HasValue) results = results.Where(o => o.OccurredOn <= Criteria.OffenseDateEnd); if (Criteria.OffenseTimeStart.HasValue) results = results.Where(o => o.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart); if (Criteria.OffenseTimeEnd.HasValue) results = results.Where(o => o.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd); 

Answer 1

I don't think you can do Between, but you can write a linq query, for example:

 where (x.CreatedOn.Date >= myDate.Date && x.CreatedOn.Date <= myDate2.Date) && (x.CreatedOn.TimeOfDay >= TimeSpan.Parse("13:00") && x.CreatedOn.TimeOfDay <= TimeSpan.Parse("15:00")) 

Answer 2

Something like that?

 results = results.Where(x => x.OccurredOn.Date >= Criteria.OffenseDateStart.Date && x.OccurredOn.Date <= Criteria.OffenseDateEnd.Date && x.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart.TimeOfDay && x.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd.TimeOfDay); 

(Assuming OccurredOn , OffenseDateStart , OffenseDateEnd , OffenseTimeStart and OffenseTimeEnd all printed as DateTime .)