How to write LINQ to SQL query for getting today date records?

Question

I want to get the today entered records using LINQ to SQL. I wrote the below code but it is returning previous date records also.

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 });

Can you please tell me how to get the today entered records only using LINQ to SQL?

Answer 1

Insetad of DateTime.Now use DateTime.Today like:

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 following simpler version, (I am not sure if that would translate into 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 });