Selecting Dates Within a Range Using SQL Queries

1. Introduction

When working with databases, we often need to filter data based on dates. Date range queries are essential whether we’re analyzing sales trends, tracking student enrollment, managing departmental schedules, tracking course completions, or managing course schedules.

In this tutorial, we’ll explore how to effectively select dates within a range using SQL queries. We’ll use the University database schema for our examples. The techniques we’ll see work on the MySQL, PostgreSQL, and Microsoft SQL Server database management systems.

2. Basic Date Filtering

To start, let’s look at how we can filter records for a specific date using the WHERE clause:

SELECT *
FROM Student
WHERE enrollment_date = '2020-01-15'

This query retrieves all students who enrolled on January 15, 2020. However, we can also select students who enrolled within a certain period. This is where date ranges come in handy.

3. Selecting Dates Within a Range

For date ranges, the BETWEEN operator comes in handy. Here’s how we can use it:

SELECT *
FROM Student
WHERE enrollment_date BETWEEN '2020-01-15' AND '2021-12-31'

This query selects all students who enrolled between January 15, 2020 and December 31, 2021. The BETWEEN operator is inclusive, meaning it includes the start and end dates.

Alternatively, we can use the >= and <= operators to achieve the same results:

SELECT *
FROM Student
WHERE enrollment_date >= '2020-01-15'
  AND enrollment_date <= '2021-12-31'

This approach gives us the same result as the operator. It’s particularly useful when we need more complex date range conditions or when we want to exclude one of the boundary dates.

4. Handling Time in Date Ranges

Sometimes, we need to consider both date and time in our queries. Let’s say we want to find students who enrolled during the first week of September 2023, including the time of enrollment:

SELECT *
FROM Student
WHERE enrollment_date >= '2020-01-15 00:00:00'
  AND enrollment_date < '2022-12-08 00:00:00'

Notably, we use < instead of <= for the end date. This ensures we don’t include enrollments from exactly midnight on December 8. It’s a small detail, but it can make a big difference in our results.

5. Using Functions for Date Range Selection

SQL provides various date functions that can make our queries more flexible and powerful. For instance, let’s find students who enrolled in the last 30 days:

SELECT *
FROM Student
WHERE enrollment_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

Here, we’re using the DATE_SUB function to subtract 30 days from the current date. This query always gives us enrollments from the last 30 days, regardless of when we run it. It’s a great example of how we can create dynamic date ranges.

We can also use date functions to find students who enrolled in a specific month:

SELECT *
FROM Student
WHERE MONTH(enrollment_date) = 01
  AND YEAR(enrollment_date) = 2021

This query selects all students who enrolled in January 2021, regardless of the day.

6. Working With Different Date Formats

Databases sometimes store dates in various formats, which can complicate our queries. Let’s say our enrollment_date is stored as a string in DD-MM-YYYY format. Here’s how to perform date range queries:

SELECT *
FROM Student
WHERE STR_TO_DATE(enrollment_date, '%d-%m-%Y') BETWEEN '2020-01-01' AND '2021-12-31'

In this query, we’re using the STR_TO_DATE function to convert the string to a date before comparing it. This approach allows us to work with dates stored in non-standard formats.

We can also use date functions to extract specific parts of a date for comparison:

SELECT *
FROM Student
WHERE YEAR(STR_TO_DATE(enrollment_date, '%d-%m-%Y')) = 2021
  AND MONTH(STR_TO_DATE(enrollment_date, '%d-%m-%Y')) BETWEEN 1 AND 5

This query selects all students who enrolled in the first five months of 2021, regardless of how the date is stored.

7. Conclusion

In this article, we’ve explored various ways to select dates within a range using SQL queries. First, we looked at basic date filtering, followed by selecting dates within a range, and finally, handling both dates and times in queries.

Furthermore, we discussed how to work with different date formats and utilize SQL functions to create dynamic date ranges. These skills are crucial for effective database management, ensuring accurate data retrieval across various scenarios.