How to write a LINQ to SQL query to get today's date records?

Question

I want to get today's entries using LINQ to SQL. I wrote the code below, but it also returns previous entries.

DateTime todaysDate = DateTime.Now; DateTime yesterdaysDate = DateTime.Now.AddDays(-1); var result = (from a in cxt.visitor.OrderByDescending(n => n.singin) where (a.singin > yesterdaysDate && a.singin <= todaysDate) select new {a.visitorid, a.visitorname, a.visitingperson, a.phonenumber, a.reasonforvisit, a.signature, a.singin }); 

Could you tell me how to get today's entries just using LINQ to SQL?

Answer 1

The DateTime.Now insert uses DateTime.Today as:

 DateTime startDateTime = DateTime.Today; //Today at 00:00:00 DateTime endDateTime = DateTime.Today.AddDays(1).AddTicks(-1); //Today at 23:59:59 var result = (from a in cxt.visitor.OrderByDescending(n => n.singin) where (a.singin >= startDateTime && a.singin <= endDateTime) select new {a.visitorid, a.visitorname, a.visitingperson, a.phonenumber, a.reasonforvisit, a.signature, a.singin }); 

or you can try the next simpler version (I'm not sure if this translates to SQL)

 var result = (from a in cxt.visitor.OrderByDescending(n => n.singin) where (a.singin.Date == DateTime.Today) select new {a.visitorid, a.visitorname, a.visitingperson, a.phonenumber, a.reasonforvisit, a.signature, a.singin });