Search code examples
c#linq-to-sql

Correct way to return results from linq to sql between dates

I have a little problem with a query.

I have two datetime textboxes and a button to search.

When I search let's say from 12/08/2021 to 12/08/2021 it returns only one result which has date 2021-08-12 00:00:00.000 because the time on the other invoices is 2021-08-12 17:38:55.740

My code is:

SearchInvoicesNotSendToMydata(fromDateEdit.DateTime, toDateEdit.DateTime);

public List<Invoices> SearchInvoicesNotSendToMydata(DateTime fromDate, DateTime toDate)
{
    List<Invoices> invoices = db.Invoices
                                .Where(p => (p.Date >= fromDate && p.Date <= toDate));

    return invoices;
}

The two variables have values

fromDate =12/8/2021 12:00:00

toDate = 12/8/2021 12:43:21

I know that it doesnt return the other invoices because of the time, I just want to know if there is an elegant way to return all invoices from date 0:00:00 to 23:59:59

enter image description here

*in image the Imerominia = Date

Solution

  • Thanks for your answers. I found how to do it. I simply used the addDays and addMilliseonds to get the right date. Here is the code for anyone who will need it.

        public List<Invoices> SearchInvoicesNotSendToMydata(DateTime fromDate, DateTime toDate)
    {
        List<Invoices> invoices = db.Invoices
                               .Where(p => (p.Date >= fromDate.Date 
                               && p.Date <= toDate.Date.AddDays(1).AddMilliseconds(-1)));
    
        return invoices;
    }