CODEWITHSUNDEEP
c#linq-to-sql
rippergr
rippergr

Reputation: 184

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

Upvotes: 0

Views: 282

Answers (3)

Laurent Gabiot
Laurent Gabiot

Reputation: 1311

EDIT

New informations lead to corrections

The Date member of the DateTime class returns a new DateTime instance, with the TimeOfDay property set at 00:00:00. It does not return a new type that would not contain a time of day information.

For instance:

DateTime.Now.TimeOfDay      // is 11:26:34.1234567
DateTime.Now.Date.TimeOfDay // is 00:00:00

So you can achieve what you need either by following @AsifRahman answer, or by using the principle of my original answer.

@AsifRahman solution is based on setting the TimeOfDay property to 00:00:00 in all the DateTime instances you will be using for comparison. This is done easily by using the Date property on all DateTime instances involved.

Notice that this solution uses an included upper bound p.Date.Date <= toDate.Date, which might not be ideal from a clarity perspective: the consequent intervals seems to be overlapping, but they are not.

The other solution is to use an exclusive upper bound and add one day to the toDate.Date value.

SearchInvoicesNotSendToMydata(fromDateEdit.DateTime, toDateEdit.DateTime);

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

    return invoices;
}

This has the benefit of being more clear (no overlapping of consequent intervals).

Upvotes: 1

rippergr
rippergr

Reputation: 184

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

Upvotes: 0

Asif Rahman
Asif Rahman

Reputation: 263

In a quick way, you can do something like this. so it'll only check and compare with dates but no time.

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

       return invoices;
     }

Upvotes: 0

Related Questions