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 TABLE TimeframeTest (
Item varchar(100),
ValidityStart date,
ValidityEnd date
);
And fill in some data rows:
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.
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:
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:
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.
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:
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:
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:
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
- 7th December, 2016: Created