Home » How To Select Data Between Two Dates In MySQL

How To Select Data Between Two Dates In MySQL

Last updated on December 27, 2020 by

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:

  1. Error After php artisan config:cache In Laravel
  2. Specified Key Was Too Long Error In Laravel
  3. AJAX PHP Post Request With Example
  4. How To Use The Laravel Soft Delete
  5. How To Add Laravel Next Prev Pagination
  6. cURL error 60: SSL certificate problem: unable to get local issuer certificate
  7. Difference Between Factory And Seeders In Laravel
  8. Laravel: Increase Quantity If Product Already Exists In Cart
  9. How To Calculate Age From Birthdate
  10. How to Convert Base64 to Image in PHP
  11. Check If A String Contains A Specific Word In PHP
  12. Dynamically Populate A Select Field’s Choices In ACF
  13. How To Find Duplicate Records in Database

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!

 
 

Leave a Comment

*

code