CODEWITHSUNDEEP
c#linqsearchdatetime
Eduardo Collado
Eduardo Collado

Reputation: 1

Linq Date comparison with same day

I have this query:

var q = from p in DB.treatments
    where p.start >= start && p.end <= end
    select p;

And in the database the end says 06/14/2012 12:00 a.m. which supposedly includes the whole 14 of june just before it turns 15 of june.

So if i send the end parameter with 06/14/2012 04:00 p.m. it doesn´t return anything, only if it were 06/15/2012 but I want it to search the whole day the 14, not 15.

So i want to search the whole day of the 14, and the datetime returns this 06/14/2012 12:00 a.m. when selecting the 14.

How do I do it?

Upvotes: 0

Views: 3765

Answers (2)

Manatherin
Manatherin

Reputation: 4187

you'll need to do a comparison with .Date. If you're querying against a database the .Date function won't work as it cannot be translated into SQL.

LINQ to Objects use 

var q = from p in DB.treatments
    where p.start >= start.Date && p.end <= end.Date
    select p;

for LINQ to SQL or LINQ to ENTITIES use 

var startDate = start.Date;  
var endDate = end.Date;  
var q = from p in DB.treatments  
        where p.start >= start && p.end <= end  
        select p;

Update: If .NET 4.0+ is being used you can use the entity functions TruncateTime. Something like:

var q = from p in DB.treatments
    where EntityFunctions.TruncateTime(p.start) >= EntityFunctions.TruncateTime(start)      
        && EntityFunctions.TruncateTime(p.end) <= EntityFunctions.TruncateTime(end)
    select p;

Upvotes: 0

David East
David East

Reputation: 32604

You need to compare on the Date property of start and end

 var q = from p in DB.treatments
         where p.start >= start.Date && p.end <= end.Date
         select p;

If start.Date is 6/14/2012 4:00 PM then you will just be comparing on 6/14/2012 when you use the Date property.

Upvotes: 3

Related Questions