How to Query Between Two Dates in MySQL?

MySql is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.

MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how to do a query between two dates in MySQL including some examples

MySQL Query Between Two Dates

Similarly to MySQL(relational database management system), the DATE data type is used to store the data. The data storage format required for MySQL database is 'YYYY-MM-DD'. The range of dates is '1000-01-01' to '9999-12-31'. Many times it happens that we want to get the data between the two dates whether it may be financial analysis, user tracking, inventory management, etc. To query between two dates we use the BETWEEN keyword to specify the range. We can perform certain operations between two dates such as SELECT, UPDATE, DELETE, etc.

Syntax:

SELECT * FROM table_name

WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';

• SELECT * FROM table_name: Retrieves all columns from the specified table.
  
• WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD': Filters the rows based on a date range, selecting rows where the date column falls between the specified start and end dates inclusively. Replace 'YYYY-MM-DD' with the desired start and end dates.

Setting up Environment

Here we will an example of an EMPLOYEE table having EMP_ID, NAME, SALARY, and JOIN_DATE as columns and we are going to perform some operations on the table.

CREATE TABLE EMPLOYEE (EMP_ID INT Primary key,
NAME VARCHAR(20),
SALARY INT,
JOIN_DATE DATE);

Insert the data on it:

INSERT INTO EMPLOYEE values
(1, 'Sahil', 15000, '2023-12-01'),
(2, 'Alen', 13000, '2023-12-08'),
(3, 'John', 14000, '2023-12-15'),
(4, 'Alex', 13000, '2023-12-22'),
(5, 'Mathew', 14000, '2023-12-31'),
(6, 'Sia', 15000, '2024-01-01'),
(7, 'David', 16000, '2024-01-10'),
(8, 'Tim', 14000, '2024-01-25'),
(9, 'Leo', 15000, '2024-02-01'),
(10, 'Tom', 16000, '2024-02-05');

EMPLOYEE table:

Example of How to query between two dates in MySQL

Example 1: Get the data of employee between two dates

Let's take an example to print the data of the employees joined between two dates

Syntax:

SELECT * FROM table_name

WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';

Let's write a query where we want to print the data of the employee whose joining date is between '2023-12-01' AND '2024-01-01'

Query:

SELECT * from EMPLOYEE
 WHERE JOIN_DATE BETWEEN '2023-12-01' AND '2024-01-01';

Output:

Explanation: The above example prints all the records of the employees whose JOIN_DATE is between '2023-12-01' AND '2024-01-01'. There are 6 employees whose JOIN_DATE is between '2023-12-01' AND '2024-01-01'. The ' * ' is used to print all the records and the BETWEEN keyword is used to specify the range of dates.

Example 2: Update the records between two dates

Here we will take an example to update the data between two dates.

Syntax:

UPDATE table_name

SET column1= value1, column2= value2, . . . . , column_n=value_n

WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';

We will write a query to UPDATE the SALARY of the employees to 15000 whose JOIN_DATE is BETWEEN '2023-12-15' AND '2024-01-25'.

Query:

UPDATE EMPLOYEE
SET SALARY=15000
WHERE JOIN_DATE BETWEEN '2023-12-15' AND '2024-01-25';

Output:

Explanation: The example above updates our records of employees whose dates of joining are between ‘2023-12-15’ and ‘2024-01-25’. The number of employees with a JOIN_DATE within the range of ‘2023-12-15’ and ‘2024-01-25’ is 6. To update the table, we use the keywords UPDATE, SET to set a new value in the record, and BETWEEN to indicate the period.

Example 3: Deleting the records between two dates

Let's take an example of deleting the records between two dates

Syntax:

DELETE from table_name

WHERE date BETWEEN YYYY-MM-DD' AND 'YYYY-MM-DD';

Let's write a query to DELETE the records of the employee whose JOIN_DATE is BETWEEN '2023-12-31' AND '2024-01-01'.

Query:

DELETE from EMPLOYEE
WHERE JOIN_DATE BETWEEN '2023-12-31' AND '2024-01-01';

Output:

Explanation: In this example, we delete those records for employees whose JOIN_DATE is between '2023-12-31' AND '2024-01-01'. We can see that two have JOIN_DATE values within this criteria. Finally, we use DELETE which is a keyword, for deleting or removing things from tables, and BETWEEN which shows that something is included in a certain range.

Conclusion

In conclusion, MySQL provides a feature to query between two dates. It is achieved by applying the keyword BETWEEN and we indicate the date range within the dates specified. The MySQL convention for the format of date is 'YYYY-MM-DD' which allows the date to be specified in the range of '1000-01-01 to 9999-12-31'. Query between two dates is quite common when it comes to user tracking, financial analysis, inventory management, etc. We can perform various operations such as SELECT, UPDATE, and DELETE while writing a query between two dates.