Selecting Valid Records

• Download source code - 749 B

Introduction

I've noticed many different ways to define the condition in WHERE clause to select only valid records based on the columns defining validity start and end dates. Quite often, the conditions contain multiple parts using greater than or less than comparisons accompanied with special OR cases for NULL values. One main reason is that both validity fields are often tested using separate conditions. If you turn the idea other way around and test if the given date is within the validity range, the comparison becomes much simpler.

Creating the Test Data and Initial Tests

The first step is to create some test data. For this, let's first create a table:

-- Create the table for test data
CREATE TABLE TimeframeTest (
   Item          varchar(100),
   ValidityStart date,
   ValidityEnd   date
);

And fill in some data rows:

-- Insert rows with defined start and end dates
INSERT INTO TimeframeTest (Item, ValidityStart, ValidityEnd) VALUES
('Started 10 days in past, ended  5 days in past',      
     DATEADD(day, -10, GETDATE()), 
     DATEADD(day, -5, GETDATE())),
('Started 5 days in past, ends 5 days in future',       
     DATEADD(day, -5, GETDATE()),  
     DATEADD(day, +5, GETDATE())),
('Starts 5 days in future, ends 10 days in future',     
     DATEADD(day, +5, GETDATE()),  
     DATEADD(day, +10, GETDATE())),
('Started 10 days in past, ends 10 days in the future', 
     DATEADD(day, -10, GETDATE()), 
     DATEADD(day, +10, GETDATE()));

Now we can try to query the data. First, the rows that are valid at the moment.

-- Which rows are valid at the moment
DECLARE @DateToCompare date;
SET @DateToCompare = GETDATE();

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN tt.ValidityStart AND tt.ValidityEnd;
GO

As you can see, the date used for comparison is the one compared to BETWEEN range. The validity start and end dates define the limits for the BETWEEN comparison. Just remember that both ends are included.

So the result from the query is:

Item
---------------------------------------------------
Started 5 days in past, ends 5 days in future
Started 10 days in past, ends 10 days in the future

Does this work properly if the date is something else than current date? Let's try to query rows that were valid six days ago or are valid one week from this moment.

Running:

-- Which rows were valid 6 days ago
DECLARE @DateToCompare date;
SET @DateToCompare = DATEADD(day, -6, GETDATE())

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN tt.ValidityStart AND tt.ValidityEnd;
GO

Results in:

Item
---------------------------------------------------
Started 10 days in past, ended  5 days in past
Started 10 days in past, ends 10 days in the future

And then again running:

-- Which rows are valid a week from now
DECLARE @DateToCompare date;
SET @DateToCompare = DATEADD(week, +1, GETDATE())

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN tt.ValidityStart AND tt.ValidityEnd;
GO

Gives:

Item
---------------------------------------------------
Starts 5 days in future, ends 10 days in future
Started 10 days in past, ends 10 days in the future

What About Missing Start or End Definition

So far so good, but what if the start or end date for the validity is unknown and the corresponding field contains a NULL value? Let's add some more data to our table.

-- Add rows with missing start or end date
INSERT INTO TimeframeTest (Item, ValidityStart, ValidityEnd) VALUES
('Started 5 days in past, no end date',              
     DATEADD(day, -5, GETDATE()),  
     NULL),
('No start date, ended 5 days in past',              
     NULL,                         
     DATEADD(day, -5, GETDATE())),
('No start date, ends 5 days in future',             
     NULL,                         
     DATEADD(day, +5, GETDATE())),
('No start date, no end date',                       
     NULL,                         
     NULL),
('Starts 5 days in future, no end date',             
     DATEADD(day, +5, GETDATE()),  
     NULL);

Now if we run the original query, it still gives just the same two rows as in the first example. Clearly, this is not correct and some of the newly added rows should be included in the result set.

The solution is to use COALESCE to replace the NULL values with the date used in the comparison. Like this:

-- Which rows are valid at the moment, take missing dates into account
DECLARE @DateToCompare date;
SET @DateToCompare = GETDATE();

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN COALESCE(tt.ValidityStart, @DateToCompare) 
                     AND COALESCE(tt.ValidityEnd, @DateToCompare);
GO

When you run the query, the result is:

Item
---------------------------------------------------
Started 5 days in past, ends 5 days in future
Started 10 days in past, ends 10 days in the future
Started 5 days in past, no end date
No start date, ends 5 days in future
No start date, no end date

What about the other test cases? Let's try.

Rows, which were valid six days ago:

-- Which rows were valid 6 days ago, take missing dates into account
DECLARE @DateToCompare date;
SET @DateToCompare = DATEADD(day, -6, GETDATE())

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN COALESCE(tt.ValidityStart, @DateToCompare) 
                     AND COALESCE(tt.ValidityEnd, @DateToCompare);
GO

Gives:

Item
---------------------------------------------------
Started 10 days in past, ended  5 days in past
Started 10 days in past, ends 10 days in the future
No start date, ended 5 days in past
No start date, ends 5 days in future
No start date, no end date

Then again, rows that are valid one week from now:

-- Which rows are valid a week from now, take missing dates into account
DECLARE @DateToCompare date;
SET @DateToCompare = DATEADD(week, +1, GETDATE())

SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN COALESCE(tt.ValidityStart, @DateToCompare) 
                     AND COALESCE(tt.ValidityEnd, @DateToCompare);
GO

And the result is:

Item
---------------------------------------------------
Starts 5 days in future, ends 10 days in future
Started 10 days in past, ends 10 days in the future
Started 5 days in past, no end date
No start date, no end date
Starts 5 days in future, no end date

Example How to Use in Code

The examples above were done using T-SQL but using the comparison is just as simple in C# or VB.NET. Here are examples.

using (SqlConnection connection = new SqlConnection("valid connection string goes here")) {
   using (SqlCommand command = new SqlCommand()) {
      command.Connection = connection;
      command.CommandText = @"
SELECT tt.Item
FROM TimeframeTest tt
WHERE @DateToCompare BETWEEN COALESCE(tt.ValidityStart, @DateToCompare) 
                     AND COALESCE(tt.ValidityEnd, @DateToCompare)";
      command.Parameters.AddWithValue("@DateToCompare", System.DateTime.Now);
      connection.Open();
      using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader()) {
         while (reader.Read()) {
            System.Diagnostics.Trace.WriteLine(reader[0]);
         }
      }
      connection.Close();
   }
}

Using connection As SqlConnection = New SqlConnection("valid connection string goes here")
    Using command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText =
"SELECT tt.Item" & vbCrLf & _
"FROM TimeframeTest tt" & vbCrLf & _
"WHERE @DateToCompare BETWEEN COALESCE(tt.ValidityStart, @DateToCompare)" & vbCrLf & _
"                     AND COALESCE(tt.ValidityEnd, @DateToCompare)"
        command.Parameters.AddWithValue("@DateToCompare", System.DateTime.Now)
        connection.Open()
        Using reader As SqlDataReader = command.ExecuteReader()
            While (reader.Read())
                System.Diagnostics.Trace.WriteLine(reader(0))
            End While
        End Using
        connection.Close()
    End Using
End Using

History

• 7^th December, 2016: Created