How to use SQL Server to check a date is within a range

[samuel_1991]

Hi, I got a question to check if a date time is within another date range (So as to check conflicting schedule)

There are various possible types of schedule:

1: One time start - end event. Typically such schedules is like a tour / outing / camp event, and hence I compare it using VB code as

If CurrentStartDate >= checkstartDate And CurrentEndDate <= checkendDate Then
    'Do whatever
'CurrentStartDate is the selected start date time of the time slot while cuurentEndDate is the selected ending date time of the time slot.
'checkstartdate and checkenddate is the previously selected starting / ending date time (As I select the schedules by listbox with multiple selection mode)
End If

2: Weekly / Monthly event. This type of schedule could have been a regular event such as a course or a regular volunteer work (Like every sunday do some community service for the year 2011)
For this, it is certainly not going to be throughout for the peroid of time and hence the 1st example cannot work.

2a: More complicated bi-weekly / bi-monthly event. Similar concept to Point 2, but it is just a bi-weekly / monthly event.

For this, I have used a code like

Dim GetWeekNoOfMonth As Integer
        GetWeekNoOfMonth = GetWeekOfMonth(CDate(CurrentStartDate))
        MsgBox(GetWeekNoOfMonth)

with the fuction of

Public Shared Function GetWeekOfMonth(ByVal [date] As DateTime) As Integer
        Dim beginningOfMonth As New DateTime([date].Year, [date].Month, 1)
        While [date].[Date].AddDays(1).DayOfWeek <> CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek
            [date] = [date].AddDays(1)
        End While
        Return CInt(Math.Truncate(CDbl([date].Subtract(beginningOfMonth).TotalDays) / 7.0F)) + 1
    End Function

in order to get the start of the event's date is in which week of the month, and if the week number falls into an odd number, then every odd number weeks will means the event will take place. However, this code will only work on the current month of startdate. (Meaning, if I plan to schedule a bi-weekly saturday community service on 2010 June 2 to 2010 November 24 will not work since 2010 June has 5 weeks where the first community service in july should be on the 2nd week instead.

3: The even more complicated ones, Exclusion dates. After the first 2 has done, I have my final question on this topic, which is like for an example, on 2010 November 17 is a public holiday in my region, and therefore I will excuse myself from the community service. (Note: The exclusion will be specified in an exact date, so there is no need to check for holidays by user's country. The reason given here is mere example only so as to see the point of this function)

Thanks a lot for answering this question, which seemed to be a complicated task.

[sknake]

You should consider using "date tables". This sort-of breaks the rules for storing data that could be calculated but when dealing with dates like this I find it to be a better solution. Create a table that stores the dates for each events and write a piece of code to backfill past days and fill future dates. You can throw a bit field on the date records to flag them as exclude dates.

This *could* be done entirely in code but it gets to be a major headache, especially when you support the application years down the road. My advice is persist date tables

[samuel_1991]

Ok, so after a few days of trying, I have come up with a function to generate a list of possible dates to exclude and with that alone, I can import back to another function to Split the strings again and add it to Exclusion listbox. From there, I store the exclusion dates on the database if that is applicable. Of course, it can be used it as showing the list of schedules involved and storing them into datatables and then comparing it again. Heres the DateGenerator code:

Public Function GenerateDate(ByVal StartDate As DateTime, ByVal EndDate As DateTime, ByVal Interval As String) As String
        Dim DateGenerated As String = "" 'This is later to be returned valued
        'Get the interval and on which day
        Dim IntervalOrDay() As String = Interval.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries)
'The format I have choosen to use in Interval is as follows:
'Number <a space> range type <a comma> list of days in numerical values(Which is separated by a space. By the way, Sunday is 0)
'Example, To show it as Every 2 weeks on Monday and Friday it would be:
'2 weeks,1 5  
        Dim alDays As New ArrayList
        Dim IntervalLength As Integer
        Dim IntervalType As String = ""
        For Each s As String In IntervalOrDay
            If s.Contains("Weeks") Or s.Contains("Months") Or s.Contains("Years") Then
                'Interval
                Dim IntervalList() As String = s.Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)
                For Each i As String In IntervalList
                    If IsNumeric(i) Then
                        IntervalLength = i
                    Else
                        IntervalType = i
                    End If
                Next
            Else
                'Day
                Dim IDay() As String = s.Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)
                alDays.Clear()
                For Each d As String In IDay
                    alDays.Add(d) 'Storing all of the numerical days into an ArrayList
                Next
            End If
        Next
        Dim daycheck As Integer = 1 'First day of week is 1 (Just a check index so as to fallback to the first applicable day for the week)
        While StartDate <= EndDate
            For a = 0 To alDays.Count - 1
                If StartDate.DayOfWeek = alDays.Item(a) Then
                    Dim DateString() As String = StartDate.ToString().Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)
                    For Each DS As String In DateString
                        If DS.Contains(":") Then
                            'Ignore (I do not want my listbox selection of exclusion dates with the time, but I want the function to consider the time as well)
                        Else
                            DateGenerated &= DS & ","
'Gotcha! We got the one of the session in this.
                        End If
                    Next

                    Exit For
                End If
            Next
            If daycheck = 7 Then
                'Reset to previous week (The logic is that an event starts on Monday, so it checks from Monday (Day 1) to Sunday (Day 7), and then revert back to day 1 so that it searches for the next monday (Day 8), if it is a weekly event)
                StartDate = StartDate.AddDays(-6) 'As the Sunday is day 0
                daycheck = 1 'reset
                If IntervalType = "Weeks" Then
                    Dim add_days As Integer = IntervalLength * 7
                    StartDate = StartDate.AddDays(add_days)
                ElseIf IntervalType = "Months" Then
                    StartDate = StartDate.AddMonths(IntervalLength)
                    'Compare again
                ElseIf IntervalType = "Years" Then
                    StartDate = StartDate.AddYears(IntervalLength)
                    'Compare again
                Else
'This is unlikely to happen, just in case (I use a dropdownlist to select these values)
                    If MsgBox("We apologise for an unusal error that had occured..", MsgBoxStyle.Exclamation, "Warning") = MsgBoxResult.Ok Then
                        DateGenerated = "" 'Clear all records as something was haywired
                        Exit While
                    End If
                End If
            Else
                daycheck = daycheck + 1
                StartDate = StartDate.AddDays(1)
            End If
        End While
        Return DateGenerated
    End Function

[samuel_1991]

And also, this below code with the use of the above DateGenerator Function, all 3 criterias will meet.

Public Sub CheckForConflicts(ByVal EventID As Integer, ByVal SelectedScheduleID As ArrayList, ByVal EventTitle As String)

        Dim objEvents As New Events
        Dim dvAllScheduleForEvent As New Data.DataView 'To get the ScheduleName etc as a whole for the event
        Dim dvScheduleForEvent As New Data.DataView
        Dim DTSchedule As New Data.DataTable
        Dim DTExistingSchedule As New Data.DataTable
        Dim dvMemberEvent As New Data.DataView
        Dim DR As Data.DataRow
        Dim alNoChanges As New ArrayList 'To be used in Step 4 to identify existing no changes
        Dim alToBeRemoved As New ArrayList 'To be used in Step 4 to identify existing to be cancelled
        'Clear existing records, if any
        alToBeRemoved.Clear()
        alNoChanges.Clear()
        DTSchedule.Dispose()
        DTExistingSchedule.Dispose()
        DTExistingSchedule.Dispose()
        If DTSchedule.Columns.Count = 0 Then
            DTSchedule.Columns.Add("StartDate")
            DTSchedule.Columns.Add("EndDate")
            DTSchedule.Columns.Add("EventID")
            DTSchedule.Columns.Add("ScheduleID")
        End If
        If DTExistingSchedule.Columns.Count = 0 Then
            DTExistingSchedule.Columns.Add("StartDate")
            DTExistingSchedule.Columns.Add("EndDate")
            DTExistingSchedule.Columns.Add("EventID")
            DTExistingSchedule.Columns.Add("ScheduleID")
        End If
        dvAllScheduleForEvent = objEvents.GetSchedule(EventID)
        dvMemberEvent = objEvents.GetMemberEvent(Request.Cookies("MemberID").Value)
        'Step 1: Find all selected schedule and save it under Data Tables
        For i = 0 To SelectedScheduleID.Count - 1
            dvScheduleForEvent = objEvents.GetScheduleFromScheduleID(SelectedScheduleID.Item(i))
            If dvScheduleForEvent.Table.Rows(0)("Interval") Is DBNull.Value Then
                'Single one off event. Straight forward
                DR = DTSchedule.NewRow
                DR("StartDate") = dvScheduleForEvent.Table.Rows(0)("StartDate")
                DR("EndDate") = dvScheduleForEvent.Table.Rows(0)("EndDate")
                DR("EventID") = EventID
                DR("ScheduleID") = SelectedScheduleID.Item(i)
                DTSchedule.Rows.Add(DR)
            Else
                'Get a list of dates
                Dim ListOfStartDate As String() = GenerateDate(dvScheduleForEvent.Table.Rows(0)("StartDate"), dvScheduleForEvent.Table.Rows(0)("EndDate"), dvScheduleForEvent.Table.Rows(0)("Interval")).ToString().Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)
                'Get possible exclusion dates
                If dvScheduleForEvent.Table.Rows(0)("Exclusion") Is DBNull.Value Then
                    For Each time As String In ListOfStartDate
                        DR = DTSchedule.NewRow
                        DR("StartDate") = time
                        DR("EndDate") = CDate(time).AddMinutes(dvScheduleForEvent.Table.Rows(0)("Duration"))
                        DR("EventID") = EventID
                        DR("ScheduleID") = SelectedScheduleID.Item(i)
                        DTSchedule.Rows.Add(DR)
                    Next
                Else
                    Dim ListOfExclusions As String() = dvScheduleForEvent.Table.Rows(0)("Exclusion")
                    Dim boolSkip As Boolean = False
                    For Each time As String In ListOfStartDate
                        For Each Exc As String In ListOfExclusions
                            If time = Exc Then
                                boolSkip = True
                                Exit For 'Target found
                            End If
                        Next
                        If boolSkip = False Then
                            DR = DTSchedule.NewRow
                            DR("StartDate") = time
                            DR("EndDate") = CDate(time).AddMinutes(dvScheduleForEvent.Table.Rows(0)("Duration"))
                            DR("EventID") = EventID
                            DR("ScheduleID") = SelectedScheduleID.Item(i)
                            DTSchedule.Rows.Add(DR)
                        End If
                        boolSkip = False 'Reset for next seesion
                    Next
                End If
            End If
        Next
        Dim boolStep2Proceed = True 'Presume it is NOT the same scheduleID, meaning fail to register
        'Step 2 find the internal conflicting tables
        For a = 0 To DTSchedule.Rows.Count - 1
            For b = 0 To DTSchedule.Rows.Count - 1
                'Compare if conflict
                If DTSchedule.Rows(a)("StartDate") >= DTSchedule.Rows(b)("StartDate") And DTSchedule.Rows(a)("EndDate") <= DTSchedule.Rows(b)("EndDate") Then
                    If DTSchedule.Rows(a)("ScheduleID") = DTSchedule.Rows(b)("ScheduleID") Then
                        boolStep2Proceed = False
                        Exit For 'Target found
                    End If
                    If boolStep2Proceed = True Then
                        'Get the conflicting group names
                        Dim AName As String = ""
                        Dim BName As String = ""
                        While AName = "" And BName = ""
                            For dv = 0 To dvAllScheduleForEvent.Table.Rows.Count - 1
                                If dvAllScheduleForEvent.Table.Rows(dv)("ScheduleID") = DTSchedule.Rows(a)("ScheduleID") Then
                                    AName = dvAllScheduleForEvent.Table.Rows(dv)("ScheduleType")
                                ElseIf dvAllScheduleForEvent.Table.Rows(dv)("ScheduleID") = DTSchedule.Rows(b)("ScheduleID") Then
                                    BName = dvAllScheduleForEvent.Table.Rows(dv)("ScheduleType")
                                End If
                            Next
                        End While
                        If MsgBox("Register for this event had failed because of group " & AName & " and " & BName & " in " & EventTitle & " are conflicting with each other!", MsgBoxStyle.Exclamation, "Event Planner - Events") = MsgBoxResult.Ok Then
                            Exit Sub
                        End If
                    End If
                End If
            Next
            boolStep2Proceed = True 'Reset
        Next
        'The old Schedule for event no use, so dispose it
        dvScheduleForEvent.Dispose()
        'Step 3, get all other events where user had signed up for
        For i = 0 To dvMemberEvent.Table.Rows.Count - 1
            dvScheduleForEvent = objEvents.GetScheduleFromScheduleID(dvMemberEvent.Table.Rows(i)("ScheduleID"))
            If dvScheduleForEvent.Table.Rows(0)("Interval") Is DBNull.Value Then
                'Single one off event. Straight forward
                DR = DTExistingSchedule.NewRow
                DR("StartDate") = dvScheduleForEvent.Table.Rows(0)("StartDate")
                DR("EndDate") = dvScheduleForEvent.Table.Rows(0)("EndDate")
                DR("EventID") = EventID
                DR("ScheduleID") = dvMemberEvent.Table.Rows(i)("ScheduleID")
                DTExistingSchedule.Rows.Add(DR)
            Else
                'Get a list of dates
                Dim ListOfStartDate As String() = GenerateDate(dvScheduleForEvent.Table.Rows(0)("StartDate"), dvScheduleForEvent.Table.Rows(0)("EndDate"), dvScheduleForEvent.Table.Rows(0)("Interval")).ToString().Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)
                'Get possible exclusion dates
                If dvScheduleForEvent.Table.Rows(0)("Exclusion") Is DBNull.Value Then
                    For Each time As String In ListOfStartDate
                        DR = DTExistingSchedule.NewRow
                        DR("StartDate") = time
                        DR("EndDate") = CDate(time).AddMinutes(dvScheduleForEvent.Table.Rows(0)("Duration"))
                        DR("EventID") = EventID
                        DR("ScheduleID") = dvMemberEvent.Table.Rows(i)("ScheduleID")
                        DTExistingSchedule.Rows.Add(DR)
                    Next
                Else
                    Dim ListOfExclusions As String() = dvScheduleForEvent.Table.Rows(0)("Exclusion")
                    Dim boolSkip As Boolean = False
                    For Each time As String In ListOfStartDate
                        For Each Exc As String In ListOfExclusions
                            If time = Exc Then
                                boolSkip = True
                                Exit For 'Target found
                            End If
                        Next
                        If boolSkip = False Then
                            DR = DTExistingSchedule.NewRow
                            DR("StartDate") = time
                            DR("EndDate") = CDate(time).AddMinutes(dvScheduleForEvent.Table.Rows(0)("Duration"))
                            DR("EventID") = EventID
                            DR("ScheduleID") = dvMemberEvent.Table.Rows(i)("ScheduleID")
                            DTExistingSchedule.Rows.Add(DR)
                        End If
                        boolSkip = False 'Reset for next seesion
                    Next
                End If
            End If
        Next
        'Step 4 find the conflicting schedules
        Dim boolStep4Proceed As Boolean = True 'Assume it is NOT the same ScheduleID
        For a = 0 To DTSchedule.Rows.Count - 1
            For b = 0 To DTExistingSchedule.Rows.Count - 1
                'Compare if conflict -- Cannot Exit For because this particular B side row does not conflict does not means the rest not
                If DTSchedule.Rows(a)("StartDate") >= DTExistingSchedule.Rows(b)("StartDate") And DTSchedule.Rows(a)("EndDate") <= DTExistingSchedule.Rows(b)("EndDate") Then
                    For i = 0 To DTSchedule.Rows.Count - 1
                        If DTSchedule.Rows(i)("ScheduleID") = DTExistingSchedule.Rows(b)("ScheduleID") Then
                            boolStep4Proceed = False
                            If alNoChanges.Contains(DTSchedule.Rows(i)("ScheduleID")) = False Then
                                alNoChanges.Add(DTSchedule.Rows(i)("ScheduleID"))
                            End If
                            'Target found, mission complete
                            Exit For
                        End If
                    Next
                    If boolStep4Proceed = True Then
                        'Get the conflicting group names
                        Dim AName As String = ""
                        Dim BName As String = ""
                        Dim BTitle As String = ""
                        While AName = ""
                            For dv = 0 To dvAllScheduleForEvent.Table.Rows.Count - 1
                                If dvAllScheduleForEvent.Table.Rows(dv)("ScheduleID") = DTSchedule.Rows(a)("ScheduleID") Then
                                    AName = dvAllScheduleForEvent.Table.Rows(dv)("ScheduleType")
                                    Exit For
                                End If
                            Next
                        End While
                        'Now the Event A side no use le, dispose it
                        dvAllScheduleForEvent.Dispose()
                        dvAllScheduleForEvent = objEvents.GetSchedule(objEvents.GetScheduleFromScheduleID(DTExistingSchedule.Rows(b)("ScheduleID")).Table.Rows(0)("EventID"))
                        While BName = ""
                            For dv = 0 To dvAllScheduleForEvent.Table.Rows.Count - 1
                                If dvAllScheduleForEvent.Table.Rows(dv)("ScheduleID") = DTExistingSchedule.Rows(b)("ScheduleID") Then
                                    BName = dvAllScheduleForEvent.Table.Rows(dv)("ScheduleType")
                                    BTitle = objEvents.GetEventFromEventID(dvAllScheduleForEvent.Table.Rows(dv)("EventID")).Table.Rows(0)("EventTitle")
                                    Exit For
                                End If
                            Next
                        End While
                        If MsgBox("Register for this event had failed because of group " & AName & " in " & EventTitle & " and " & BName & " in " & BTitle & " are conflicting with each other!", MsgBoxStyle.Exclamation, "Event Planner - Events") = MsgBoxResult.Ok Then
                            Exit Sub
                        End If
                    End If
                End If
            Next
            boolStep4Proceed = True ' Reset
        Next
End Sub