A Safer Alternative to BETWEEN When Filtering Dates

The most common "gotcha" when working with the SQL BETWEEN statement is when you lose the last day's worth of values because you forgot about the time portion of a DateTime field.

Consider the following sample data:

Let's say we want to run a report that includes all records created in November 2021.  Such a report should include IDs 2 through 8.

Sound easy?  Let's give it a whirl.

FAILURE #1: Unexpected Time Portions

At first glance, the following query would appear to return all the rows created in November of 2021:

SELECT * FROM Table1
WHERE DateCreated BETWEEN #11/1/2021# AND #11/30/2021#

The BETWEEN statement is inclusive, which means that it will include records that match the two limits (i.e., #11/1/2021# and #11/30/2021#).  The above query will return what you expect for a field that does not include times.  But the data in Table1 does include times.  The query above will only return IDs 2 through 6.  

Record 7 and 8 are excluded from the results because they occur during the day on the last day of the period.

FAILURE #2: Incrementing the End Date

Let's try to add a day to the upper date in the condition:

SELECT * FROM Table1
WHERE DateCreated BETWEEN #11/1/2021# AND #12/1/2021#

The results now include IDs 7 and 8, but they are still not correct.

Record 9 is included in the results because there is no time portion on the DateCreated field of that row.

SUCCESS #1: Greater Than / Less Than

The truth is you CANNOT solve this problem using the BETWEEN statement.  

The solution is to use greater than and less than signs in place of the BETWEEN statement:

SELECT * FROM Table1
WHERE DateCreated >= #11/1/2021#
  AND DateCreated <  #12/1/2021#

Notice that we use greater than or equal on the first condition, but just less than on the second condition.  This is intentional.  It's what allows us to increment the end date by one day without having to worry about row 9 appearing in our results.

The above query will correctly display rows 2 - 8 of Table1.

SUCCESS #2: Using the DateAdd Function

Of course, you don't want to be telling your users to enter an end date of December 1 when they only want to see records in November.  That would be very confusing.

Instead, Ben Clothier recommends using the DateAdd function to calculate this based on the user-supplied end date:

One thing to be aware of is that the DateAdd function will preserve the time portion of the date (if there is one):

SUCCESS #3: Using the BetweenDays Function

Another "gotcha" when working with the BETWEEN statement is that the order of the items matters for most SQL dialects outside of the Jet/ACE database engine.

In other words, while the following query would work for (date-only) fields in Access, it will return zero results in SQL Server:

WHERE DateCreated BETWEEN #11/30/2021# AND #11/1/2021#

It would be nice if our ultimate solution could take this behavior into account so that we can use Jet's "order doesn't matter" approach to the BETWEEN statement even if our table is in SQL Server.

Oftentimes, we need to generate date-based WHERE conditions in code.  This is a common practice when previewing reports, for example.  To simplify that task, I wrote a quick function to generate a pair of conditions to filter a query between two dates.

The function, BetweenDates, includes the following features:

• avoids the last day logic error for DateTime fields with a time component
• the chronological order of Date1 and Date2 is irrelevant
• time portions included as part of Date1/Date2 are ignored
• the two conditions are wrapped in parentheses to ensure proper order of operations when used as part of a larger WHERE condition

Sample Code: BetweenDates Function

NOTE: The comment lines that begin with >>> in the code below are Python-style doc tests.

The code also uses my date quoting function, Dt().  See here for source code.

'---------------------------------------------------------------------------------------
' Procedure : BetweenDates
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/betweendates/
' Date      : 12/11/2021 - 12/13/2021
' Purpose   : Generates a pair of WHERE conditions to filter a query inclusively between two dates
' Notes     - avoids the last day logic error for DateTime fields with a time component
'           - the chronological order of Date1 and Date2 is irrelevant
'           - time portions included as part of Date1/Date2 are ignored
'           - the two conditions are wrapped in parentheses to ensure proper order of operations
'
'>>> BetweenDates("MyField", #11/1/2021#, #11/30/2021#)
' (MyField >= #11/1/2021# AND MyField < #12/1/2021#)
'>>> BetweenDates("MyField", #11/30/2021 23:45#, #11/1/2021 23:59#)
' (MyField >= #11/1/2021# AND MyField < #12/1/2021#)
'>>> BetweenDates("MyField", #1/1/2021#, #12/31/2021#)
' (MyField >= #1/1/2021# AND MyField < #1/1/2022#)
'>>> BetweenDates("MyField", #1/1/1980#, #1/1/1980#)
' (MyField >= #1/1/1980# AND MyField < #1/2/1980#)
'---------------------------------------------------------------------------------------
'
Function BetweenDates(FldName As String, Date1 As Date, Date2 As Date) As String
    Dim MinDate As Date, MaxDate As Date
    If Date1 < Date2 Then
        MinDate = DateValue(Date1)
        MaxDate = DateValue(Date2)
    Else
        MinDate = DateValue(Date2)
        MaxDate = DateValue(Date1)
    End If
    
    Dim UpperDate As Date
    UpperDate = DateAdd("d", 1, MaxDate)
    
    BetweenDates = "(" & FldName & " >= " & Dt(MinDate) & " AND " & _
                  FldName & " < " & Dt(UpperDate) & ")"
End Function

Referenced articles

Image by photosforyou from Pixabay

UPDATE [2021-12-13]: Added link to article with Dt() function source code and updated BetweenDates function to use DateValue() instead of Int() for better reliability and to clarify intent (thanks, Ben!).