SQL Standard

SQL Date Filtering

Filtering the data based on the date value is a common occurrence when working with databases. For example, it can allow us to fetch the data from a specific time frame, aggregate the results based on a given date, identify the trends patterns over time, and other time-related operations.

It is therefore an important skill for any database developer. SQL provides us with various tools to filter the dates within a given dataset.

Join us in this post as we explore the various methods and techniques that we can use to filter the dates in the world of SQL.

Prerequisites:

Before we dive into the practical examples and applications, let us break down what you need for this one.

In this post, we will try to adhere to the methods that can apply to nearly all SQL databases. However, for demonstration purposes, we use the MySQL version 8 with the Sakila sample database.

You are, however, free to download and use any dataset that you wish. We will be sure to point out if a provided method can work in other databases and provide an alternative if any.

Filter a Specific Date

The most basic date filtering operation is where we need to retrieve a record or multiple records for a specific date.

In such a case, we can use the WHERE clause followed by the date column and the actual date value that we wish to retrieve.

For example, suppose we want to find out the rental records that occurred on May 24th, 2005. We can run a query as follows:

SELECT *
FROM rental
WHERE rental_date = '2005-05-24 23:03:39';

 

In this case, we provide the date that we wish to filter as a timestamp value. This is because the “rental_date” column stores the values as timestamp.

Filter the Date Range

The second common operation is filtering the data based on a specific date range. For example, suppose we wish to retrieve the rentals that occurred between May 2005 and June 2005.

We can run the query as follows:

SELECT
    *
FROM
    rental
WHERE
    rental_date BETWEEN '2005-04-01 00:00:00' AND '2005-06-01 00:00:00';

 

In this case, we use the AND operator to join two values. The WHERE clause is used where any value from the “rental_date” column must be between the two ranges.

An example output is as follows:

Filter the Date Component

In other cases, instead of using a date range using the literal values, we can extract the specific date components from the value and filter based on that.

For example, instead of specifying from 2005-04-01 and 2005-06-01, we can extract the month of May and filter any data that is within that month.

In MySQL, we can use a function like the MONTH() function to achieve this as demonstrated in the following example:

SELECT
    *
FROM
    rental
WHERE
    MONTH(rental_date) = 5;

 

In this case, the MONTH(rental_date) extracts the month part from the date. Then, we can use this value to filter where the value is equal to 5, May.

Conclusion

In this tutorial, we learned about one of the most fundamental and common tasks in SQL where we filter the data based on the date value. We learned how to extract the various components from a date and use it to filter the date and more.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list