How to validate dates falling not occupied in database

Question

What I want to check date ranges falling in my provided date or not. For example I have a table with "FROM" and "TO" dates.

I just want to check that given date doesn't fall in dates in database.

Data in Database

FromDate  and TODate 

1 Aug 2012  ------  4 Aug 2012

5 Aug 2012 ------   11 Aug 2012

12 Aug 2012 ------  15 Aug 2012

Example From and To Dates

FromDate      and      ToDate 

1 Aug 2012 ------   2 Aug 2012  **Should Return INVALID**

5 Aug 2012  ------  11 Aug 2012 **Should Return INVALID**

10 Aug 2012 ------   10 Aug 2012  **Should Return VALID**

15 Aug 2012  ------ 15 Aug 2012 **Should Return INVALID**

Can someone give any idea?

I am using LINQ to SQL in querying from database.

EDIT: just want to make sure that my TO and From dates dont conflicts with the dates in Database

Could you please rephrase your question or provide some additional information/ code? I don't understand what you are asking for. — Drasive, Jun 18 '13 at 07:35
Take a look on [range implementation](http://stackoverflow.com/a/4781727/470005) — Sergey Berezovskiy, Jun 18 '13 at 07:36
Updated. 10 aug is valid as this is not falling in any dates in database — DDR, Jun 18 '13 at 07:41
Well then your title is misleading coz there is no range here, just need to filter on the values which are not available in either of the columns — V4Vendetta, Jun 18 '13 at 07:45

Gábor Plesz · Accepted Answer · 2013-06-18T09:41:05.290

The fact that the intervals start and end point is correct, do not check it. We examine the existing interval intersects whether the interval in question:

void Main()
{

var ExistIntervals = new HashSet<Interval>();
//1 Aug 2012 4 Aug 2012
//5 Aug 2012 11 Aug 2012
//12 Aug 2012 15 Aug 2012
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 1), 
                                  To = new DateTime(2012, 8, 4) });
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 5), 
                                  To = new DateTime(2012, 8, 11) });
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 12), 
                                  To = new DateTime(2012, 8, 15) });

var QueryIntervals = new HashSet<Interval>();
//1 Aug 2012 2 Aug 2012 INVALID
//5 Aug 2012 11 Aug 2012 INVALID
//10 Aug 2012 10 Aug 2012 VALID
//15 Aug 2012 15 Aug 2012 INVALID
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 1), 
                                  To = new DateTime(2012, 8, 2) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 5), 
                                  To = new DateTime(2012, 8, 11) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 10), 
                                  To = new DateTime(2012, 8, 10) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 15), 
                                  To = new DateTime(2012, 8, 15) });

var result = QueryIntervals.Where( x=> !ExistIntervals.Any(
                                         y=>(y.From <= x.From && x.From <= y.To)
                                         || (y.From <= x.To && x.To<=y.To)
                                                          )
                                 );
result.Dump();                                                         

}

public class Interval
{
  public DateTime From { get; set; }
  public DateTime To { get; set; }
}

The result is an empty set.

(the result is an empty set, since 10 Aug 2012th in this interval: 5 Aug 2012 ------ 11 Aug 2012)

You can test with LinqPad.

Update:

Suppose that the table name "CompaitnDates", the context name "CompaitnContext" and to determine the interval of two variables: "checkFrom", "checkTo".

var checkFrom = new DateTime(2012, 8, 10);
var checkTo = new DateTime(2012, 8, 10);
var db = new CompaitnContext();

then the test may be as follows:

var isValid = !db.CompaitnDates.Any(
                                    y=>(y.From <= checkFrom && checkFrom <= y.To)
                                    || (y.From <= checkTo && checkTo<=y.To)
                                   );

Didnt catch that, I have dates in Database. I just wanat to check this from database with LinqToSql — DDR, Jun 18 '13 at 08:55
This line is your friend: var result = QueryIntervals.Where( x=> !ExistIntervals.Any(y=>(y.From <= x.From && x.From <= y.To) || (y.From <= x.To && x.To<=y.To))); Assuming that lists the intervals tested in "QueryIntervals" and the name of the existing intervals in EF "ExistIntervals". But if you give the name of the table and to use the tested interval, it may be that I can still help. — Gábor Plesz, Jun 18 '13 at 09:02

How to validate dates falling not occupied in database

1 Answers1