MySQL select rows with date range[Solved]

In this article, we will be looking into selecting the records that fall between a particular date range.

Table of Contents

• Select rows with date range using BETWEEN
• Select rows with date range using <= and >=
• Select rows with date range using DATE() function

Let’s get started by looking into the table customer_sale_details, which we will be using across the article.

From the table customer_sale_details we will try to select the record that falls between a specific date range. 

Select rows with date range using BETWEEN

The BETWEEN operator, when used with SELECT, will get the values within a given range. Between operator can be used with datatype Date, Text, Numbers. Observe the below query to get the records of customer purchases within the year 2020.

SELECT 
    *
FROM
    customer_sale_details
WHERE
    date_of_purchase BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 00:00:00'
ORDER BY customer_id ASC; 

Output:-

Figure 2.1 shows all the rows where the date_of_purchase is between 1st-Jan-2020 to 31st-Dec-2020.

Let us examine another example select the rows for the year 2019.

SELECT 
    *
FROM
    customer_sale_details
WHERE
    date_of_purchase BETWEEN '2019-01-01' AND '2019-12-31' 
ORDER BY customer_id ASC; 

Output:-

We can see that in figure 2.2, the results retrieved were for all the purchases done in 2019. We have omitted the time in the query and just used the date part from the datetime expression.

Select rows with date range using <= and >=

Another way to get the rows within a specific range of data is by using the <= and >= operators. Observe the below query to get the records of customer purchases within the year 2020.

SELECT 
    *
FROM
    customer_sale_details
WHERE
    date_of_purchase >= '2020-01-01'
        AND date_of_purchase <= '2020-12-31';

Output:-

Here we got the results where the date_of_purchase is greater than or equal to ‘2020-01-01’ and lesser than or equal to ‘2020-12-31.’

Taking another example- get all the records for the customer purchases done before November-2020

SELECT 
    *
FROM
    customer_sale_details
WHERE
    date_of_purchase <= '2020-11-01';

Output:-

The results shown are only the records where date_of_purchase was before November 2020.

Select rows with date range using DATE() function

Date() function will extract the date part from the datetime expression in MySQL. In the below query, let us see how to use it to get the records within a specific range — year 2020 in our case.

SELECT 
    *
FROM
    customer_sale_details
WHERE
    DATE(date_of_purchase) BETWEEN DATE('2020-01-01 00:00:00') AND DATE('2020-12-31 23:59:00')
ORDER BY customer_id ASC;

Output:-

The output shows the records with date_of_purchase between 1st-Jan-2020 and 31-Dec-2020.

**Date() function might not be the right solution in terms of performance if there is a lot of data in the table as it will do a full scan and not use the indexes.

We hope this article helped you to get rows within a range. Good Luck!!!