Remove time from Date Time in Query-CodePudding

In my web application, I want to show data only between 2 days ago that records from ''DateTime.Today''.

to get the date of 2 days ago date I tried

DateTime twoDaysAgo = DateTime.Today.AddDays(-2);

And then in the query

 smcNews = (from n in db.NewsShare 
            join e in db.CreateEmployee on n.Created_By equals e.Id join d in db.CreateDepartment on e.DepId equals d.Id 
            where n.CreatedDate > twoDaysAgo && n.CreatedDate <= DateTime.Today 
            select new NewsShareViewModel {
              Id = n.Id,
              UserName = e.EmpName,
              Department = d.Department,
              Message = n.Comment,
              UserId = n.Created_By,
              CreatedDate = n.CreatedDate.ToString()
              }).ToList();

it won't return data. I checked the value of twoDaysAgo and it's like {12/29/2021 12:00:00 AM}

the data in the CreatedDate is 2021-12-31 13:43:19.957

So I there any way to get this query right by removing the time from the date or something ?

CodePudding user response：

n.CreatedDate <= DateTime.Today is effectively n.CreatedDate <= 2021-12-31 00:00:00.

the data in the CreatedDate is 2021-12-31 13:43:19.957

A datetime of 2021-12-31 13:43:19.957 is not "less than or equal to" 2021-12-31 00:00:00

Remove the && n.CreatedDate <= DateTime.Today condition - it's doing nothing for you (other than excluding any data you created today), assuming records cannot be created in the future, and assuming you want all records created since midnight two days ago

I there any way to get this query right by removing the time from the date or something ?

Avoid doing this wherever possible; aim to always use a range of fixed constant values from/to when you're searching data in a range.

Do not manipulate table data in a Where clause, unless you want to create queries that cannot use indexes (and become a performance issue as a result)

CodePudding user response：

A datetime always includes a time. You cannot avoid this.

If your start date is the 29th, that means your Today value is the 31st at midnight. 2021-12-31 13:43:19.957 is after midnight, so it falls out of the range you specified.

Instead, use the next day (the 1st at midnight) as the upper boundary.

DateTime start = DateTime.Today.AddDays(-2); //29th
DateTime end = DateTime.Today.AddDays(1);    //1st

And then filter using:

where n.CreatedDate >= start && n.CreatedDate < end

Note the use of >= and <. This makes sure to include everything from the 29th, 30th and 31st; but nothing from the 1st.

CodePudding user response：

You won't be able to use n.CreatedDate.Date with EF. I'm guessing that you're using an EF version for .Net Framework as DateTime.Date is supported in EF Core

Instead you should use DbFunctions.TruncateTime.

DbFunctions.TruncateTime(n.CreatedDate) >= twoDaysAgo &&
DbFunctions.TruncateTime(n.CreatedDate) <= DateTime.Today

Update - Don't do it this way - it'll be relatively slow compared to correctly ranging your query