27254

Checking for date overlap across multiple date range objects

Question:

I have several records in a database that have Start and End Dates

09/15/2011 - 09/30/2011 10/15/2011 - 10/22/2011 11/01/2011 - 11/15/2011

When user stores a record, I need to make sure dates don't overlap. My simple code checks date ranges within a specific record (e.g. user enters 9/16/2011 or 10/21/2011, I throw an exception.)

But, on the slim chance a user gets creative (e.g. 10/14/2011 - 10/23/2011 or even 10/14/2011 to 11/16/2011), now they have circumvented my check.

BTW, the user could enter 10/14/2011 to 10/23/2011 if they were editing the record that contained values 10/15/2011 - 10/22/2011.

So, I'm trying to solve this riddle with a linq query. However, what I have isn't working exactly right.

<hr />

<strong>UPDATE</strong> Nevermind about code not working. While trying to provide an example to expand on Miika's repsonse, I found my answer. So, giving credit to Miika for pointing me in the right direction and posting my working code below:

Here's my code:

Private Sub CheckForOverlap(myMonth As Messages.MyMonth) Dim am As New MyMonth() Dim amCollection As Messages.MyMonthCollection Dim overlappingMyMonthDate As Boolean = False Dim sErrorMsg As String = "" '...non-applicable code omitted Dim query = From s In amCollection _ Let s1 As MyMonth = CType(s, MyMonth) _ Where s1.AttendanceMonthID <> attendanceMonth.AttendanceMonthID And _ (CDate(attendanceMonth.StartDate) < CDate(s1.StartDate) And CDate(attendanceMonth.EndDate) > CDate(s1.EndDate)) _ Select s1 If query.Count > 0 Then sErrorMsg = "Dates entered surround another entry" End If If overlappingMyMonthDate Then Throw New Exception(sErrorMsg) End If End Sub End Class

It all came down a LINQ query.

Answer1:

Do you need to do it in code or would SQL be an option? If the data is in a database, you could use the following query to check for overlaps.

SELECT COUNT(*) FROM Table1 WHERE Table1.StartDate < 'endCheckDate' AND Table1.EndDate > 'startCheckDate'

This will return a count of the number of overlaps found. 'endCheckDate' and 'startCheckDate' are your new query values (in date format). If your data is in a object collection in memory, then you could use LINQ. If you need help with a LINQ statement, let me know.

Recommend

  • Why Can't I Inherit IO.Directory?
  • adding tracking parameter is treating url as differnt
  • Change text of CreateUser button in CreateUserWizard control
  • `numpy.diff` and `scipy.fftpack.diff` giving different results when differentiating
  • Dealing with context classes in Python 2.4
  • Android: playing audio files in /res/raw by file name
  • Einstein's riddle
  • Writing Unittest for generic classes… best approach?
  • LSEnvironment section of info.plist take no effects.
  • UITableView In Edit Mode - Pressing Delete Makes My App Crash
  • SCSS Language Injection in Pycharm
  • FluentMigrator Failed Migrations Don't Rollback?
  • Microsoft Chart Controls for Microsoft .NET Framework 4.0
  • How to change default stop edit behavior in jtable
  • goJS dropdown remove items
  • Many to Many in Linq using Dapper
  • Multiple Left Join LINQ-to-entities
  • Exception creating JSON with LINQ
  • Linq Merge lists
  • Silverlight DependencyProperty.SetCurrentValue Equivalent
  • Zurb Foundation _global.scss meta styles for js?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • What is Eclipse's Declaration View used for?
  • Fill an image in a square container while keeping aspect ratio
  • Join two tables and save into third-sql
  • Convert array of 8 bytes to signed long in C++
  • Display Images one by one with next and previous functionality
  • How to make Safari send if-modified-since header?
  • Matrix multiplication with MKL
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • SQL merge duplicate rows and join values that are different
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • File not found error Google Drive API
  • Add sale price programmatically to product variations
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Converting MP3 duration time