CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
Computer
Computer

Reputation: 2227

Get date range after date selection

I have a database table (customer) with 4 columns

Name
Lastname
StartDate
StopDate

The start and stop dates can run over several days, i.e. 

StartDate          StopDate

01.01.2015 00:00 - 01.01.2015 01:00 - Period is 1 hour
01.01.2015 00:00 - 01.05.2015 01:00 - Period is 4 days
01.01.2015 00:00 - 01.10.2015 01:00 - Period is 9 days

I have an ASP .Net calendar control. When a user clicks a date on this calendar i would like to return the dates in the following manner

If 01.01.2015 is selected the ALL 3 records above would be returned If 02.01.2015 is selected only the last 2 records would be returned (as the 1st record has now expired) If 03.01.2015 is selected only the last 2 records would be returned (as the 1st record has now expired) If 06.01.2015 is selected only the last record would be returned (as the top 2 have now expired)

Hope this makes sense?

So my attempt to carry this out has led some confusion using Linq to SQL

Private IQueryable<Insurer> GetInsurers(DateTime UserSelectedDate)
{
    return db.Insurers(dt=> dt.StartDate >= UserSelectedDate && dt.StopDate <= UserSelectedDate)
}

I've tried other variations including some on the internet but not getting the results im after which usually means im missing something critical here (or ive exhausted myself).... Any idea?

Upvotes: 0

Views: 64

Answers (1)

Yahya
Yahya

Reputation: 3444

Just swap the position of your variable in query.

private IQueryable<Insurer> GetInsurers(DateTime UserSelectedDate)
{
    return db.Insurers(dt=> UserSelectedDate >= dt.StartDate && UserSelectedDate <= dt.StopDate)
}

Which means UserSelectedDate should be after or same as start date and should end before or at end date.

Upvotes: 1

Related Questions