检查多个日期范围对象之间的日期重叠 [英] Checking for date overlap across multiple date range objects

问题描述

我在数据库中有几条记录，这些记录具有开始日期和结束日期

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

当用户存储记录时，我需要确保日期不重叠. 我的简单代码检查特定记录内的日期范围(例如，用户输入2011年9月16日或2011年10月21日，则抛出异常).

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.)

但是，用户获得创意的机会很小(例如10/14/2011-10/23/2011甚至10/14/2011到11/16/2011)，现在他们绕开了我的支票.

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.

顺便说一句，如果用户正在编辑包含值10/15/2011-10/22/2011的记录，则用户可以输入10/14/2011至10/23/2011.

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.

因此，我正在尝试使用linq查询解决此难题.但是，我的工作不完全正确.

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

更新没关系，代码不起作用.在尝试提供扩展Miika的答复的示例时，我找到了答案.因此，要感谢Miika指出正确的方向并在下面发布我的工作代码:

UPDATE 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:

这是我的代码:

        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

所有这些都来自LINQ查询.

It all came down a LINQ query.

推荐答案

您是否需要在代码中执行此操作，或者可以选择使用SQL?如果数据在数据库中，则可以使用以下查询检查是否存在重叠.

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'

这将返回找到的重叠数的计数. "endCheckDate"和"startCheckDate"是您的新查询值(日期格式).如果您的数据在内存中的对象集合中，则可以使用LINQ.如果您需要有关LINQ语句的帮助，请告诉我.

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.

这篇关于检查多个日期范围对象之间的日期重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！