Linq-to-SQL to search only DATE portions of a date

Question

I need to get data based on date range like:

SELECT * FROM MyTable WHERE 
myDate >= 'May 17 2012' AND 
myDate <= 'May 17 2012'

I have written this query in LINQ , the issue is that LINQ is converting this query to:

SELECT * FROM MyTable WHERE 
myDate >= 'May 17 2012 12:00:00:000AM' AND 
myDate <= 'May 17 2012 12:00:00:000AM'

The problem is that LINQ is also considering the time part but I need to search only on date part.

Can anyone help me?

Thanks.

Answer 1

Just wanted to share with you that i found the solution!

The EntityFunctions provide us TruncateTime(), which can used for this purpose.

EntityFunctions.TruncateTime

 from e in MyContext.MyTable.Where(a =>
 EntityFunctions.TruncateTime(a.DateTimeColumn) ==   
 EntityFunctions.TruncateTime(DateTime.Now))  select e;

EDIT: For EntityFramework 6 and above:

 from e in MyContext.MyTable.Where(a =>
 DbFunctions.TruncateTime(a.DateTimeColumn) ==   
 DbFunctions.TruncateTime(DateTime.Now))  select e;

I found it here.

Answer 2

I was looking for this answer today also - found it here Comparing Dates in Linq To SQL

I also double checked with the profiler to make sure its being translated to SQL

from e in MyContext.MyTable.Where(a => a.DateTimeColumn.Date == DateTime.Now.Date)
select e;

Answer 3

You have two options

from t in context.MyTable
where t.MyDate <= maxDate.AddDays(1)
select t

or

from t in context.MyTable
where t.MyDate.Date <= maxDate.Date
select t

The first might give better performance since the second might not be able to use your database indexes correctly. But the second is more obvious in intent.

Answer 4

You aren't actually searching only by date part; behind the scenes that date is a DateTime. So, to get the rows for one day think of it this way:

I want to get all rows that have MyDate greater or equal to May 17 midnight and less than May 18 midnight.

The query below does that.

var startDate = new DateTime(2012, 05, 17);
var endDate = new DateTime(2012, 05, 18);
var result = 
from row in context.MyTable
where row.MyDate >= startDate &&
      row.MyDate < endDate
select row;