How To Select Data Between Two Dates In MySQL

Very frequently asked questions, how to select data between two dates in MySQL or PHP. There could be numerous ways to do this, but we will see some of the easy & good ways. We hope you like it. So are you ready to do it? Let’s just jump right into it.

Select Data From Database Between Two Dates

01 Use BETWEEN….AND…. Comparison Operator

As the name suggests, It is used to check whether a value is within a range or not.

Syntax:

`your_column_name` BETWEEN 'start-value' AND 'end-value'

Syntax Example:

`order_date` BETWEEN '2020-04-05' AND '2020-04-10' // Retrun data including 05-04-2020 to 10-04-2020

`order_date` BETWEEN '2020-04-05' AND CURDATE() // Retrun data including 05-04-2020 to Current Date

Please note that BETWEEN…AND… comparison operator returns the records with including both start and end value.

Let’s see some real-world examples of BETWEEN…AND…. comparison operator

Table Name: order

Let’s assume that you have the following order table in your database and you would like to fetch the records between ‘2020-04-15’ to ‘2020-04-30’

ID         | user_id| qty | order_date |
-----------+---------+-----+------------+
1          | 5      | 7   | 2020-04-06 |
2          | 7      | 14  | 2020-04-17 |
3          | 8      | 1   | 2020-04-20 |
4          | 100    | 5   | 2020-04-25 |
5          | 101    | 7   | 2020-04-30 |
6          | 17     | 8   | 2020-04-15 |
7          | 25     | 2   | 2020-05-25 |
8          | 24     | 1   | 2020-05-15 |
9          | 10     | 10  | 2020-05-28 |

So the query will look like as below

SELECT * FROM `order` WHERE `order_date` BETWEEN '2020-04-15' AND '2020-04-30'

Output:

ID         | user_id| qty | order_date |
-----------+---------+-----+------------+
2          | 7      | 14  | 2020-04-17 |
3          | 8      | 1   | 2020-04-20 |
4          | 100    | 5   | 2020-04-25 |
5          | 101    | 7   | 2020-04-30 |

02 Use > AND < Comparison Operator

You can also use > (Greater Than) & < (Less Than) comparison operator to fetch records between two dates.

Syntax:

`column-name` >= 'start-value' AND `column-name` <= 'end-value'

So your query will look like as below for the same above-mentioned order table.

SELECT * FROM `order` WHERE `order_date` >= '2020-04-15' AND `order_date` <= '2020-04-30'

Examples

Here, we will show you different examples of date ranges like selecting records between the two years, month, etc. Let’s see it.

01 Select Records Between Two Years

To select records between two years, we need to extract the year from the date so in such a case YEAR() function will be used. You just need to place your date or column inside the YEAR() function.

Let’s assume you want to fetch all the records between 2019 & 2020 so it’s query will look like below.

SELECT * FROM `order` WHERE YEAR(order_date) BETWEEN '2019' AND '2020'

02 Select Records Between Two Months

To select records between two months, we need to extract the month from the date so in such a case MONTH() function will be used. You just need to place your date or column inside the MONTH() function.

Let’s assume you want to fetch all the records of MAY & JUNE months so it’s query will look like below.

SELECT * FROM `order` WHERE MONTH(order_date) BETWEEN '05' AND '06'

03 Select All Records Between Two Months of Specific Year

SELECT * FROM `order` WHERE MONTH(order_date) BETWEEN '05' AND '06' AND YEAR(order_date) = '2020'

04 Select All Records of Specific Month

SELECT * FROM `order` WHERE MONTH(order_date) = '05'

05 Select All Records of Current Month

SELECT * FROM `order` WHERE MONTH(order_date) = MONTH(CURDATE())

06 Select All Records of Specific Year

SELECT * FROM `order` WHERE YEAR(order_date) = '2020'

07 Select All Records of Current Year

SELECT * FROM `order` WHERE YEAR(order_date) = YEAR(CURDATE())

08 Select All Records of Specific Month & Specific Year

SELECT * FROM `order` WHERE MONTH(order_date) = '05' AND YEAR(order_date) = '2020'

That’s it for now. We hope this article helped you to select data between two dates in MySQL.

Additionally, read our guide:

Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you in advance 🙂 Keep Smiling! Happy Coding!