This article will be looking into how to use BETWEEN operator with dates in MySQL select where statements. We will be going through a few examples to demonstrate the concept.
Table of Contents:
- Select data between two dates in MySQL
- MySQL select data between two today and 7 days
- MySQL select where date between two columns
Let us get started by making the sample data to be used across the examples. Create a table named patient_clinical_dates, followed by inserting some rows into it.
#create the table patient_clinical_dates; CREATE TABLE patient_clinical_dates ( patient_id INT, patient_name VARCHAR(255), encounter_date DATE, opd_appointment_date DATE, catscan_appointment_date DATE ); #insert rows into patient_clinical_dates; INSERT INTO patient_clinical_dates(patient_id,patient_name,encounter_date, opd_appointment_date,catscan_appointment_date) VALUES(1,"Justin",subdate(curdate(), 10),'2021-08-6',adddate(curdate(), 10)), (2,"Veronica",subdate(curdate(), 40),current_date(),adddate(curdate(), 10)), (3,"George",subdate(curdate(), 5),'2021-04-03',adddate(curdate(), 09)), (4,"Veena",subdate(curdate(), 15),'2021-04-09',adddate(curdate(), 4)), (5,"Tanya",subdate(curdate(), 25),'2021-04-08',adddate(curdate(), 5)), (6,"Peter",subdate(curdate(), 5),'2021-04-04',adddate(curdate(), 23)), (7,"Smitha",subdate(curdate(), 35),'2021-05-06',adddate(curdate(), 21)), (8,"Walter",subdate(curdate(), 12),'2021-04-06',adddate(curdate(), 13)), (9,"Daniel",subdate(curdate(), 10),'2021-04-10',adddate(curdate(), 15)), (10,"Erick",subdate(curdate(), 2),adddate(curdate(), 20),adddate(curdate(), 15)), (11,"Atharv",subdate(curdate(), 5),adddate(curdate(), 28),adddate(curdate(), 2)), (12,"Eva",subdate(curdate(), 5), adddate(curdate(), 38),adddate(curdate(), 1));
To view the snapshot of the table patient_clinical_dates, execute:
SELECT * FROM patient_clinical_dates;
Output:-

Select data between two dates in MySQL
Example: Get the ids and names of the patients along with CAT scan appointment date whose catscan_appointment_date falls between ‘2021-04-18’ and ‘2021-04-28’
Observe the below query for the solution.
Frequently Asked:
- Inserting multiple rows in MySQL
- Error Code: 1364 [Solved] Field doesn’t have a default value
- How to get multiple counts in single MySQL query
- MySQL create table if not exists
SELECT patient_id, patient_name, catscan_appointment_date FROM patient_clinical_dates WHERE DATE(catscan_appointment_date) BETWEEN '2021-04-18' AND '2021-04-28';
Output:-

MySQL select data between two today and 7 days
Example: Get the details from patient_clinical_dates where opd_appointment_date is between today and 7 days later
Observe the below query for the solution.
SELECT * FROM patient_clinical_dates WHERE DATE(opd_appointment_date) BETWEEN CURDATE() AND ADDDATE(CURDATE(), 7);
Output:-

Example: Get the ids and names of the patients whose encounter_date falls between opd_appointment_date and catscan_appointment_date
Observe the below query for the solution.
SELECT patient_id, patient_name FROM patient_clinical_dates WHERE encounter_date BETWEEN opd_appointment_date AND catscan_appointment_date;
Output:-

Reference:-
- DATE(expression): Will get the date value from the DATE or DATETIME expression passed in as a parameter.
- CURDATE(): Will return current date either in ‘YYYY-MM-DD‘ or ‘YYYYMMDD‘ format depending on if the curdate() function is used in a string or numeric context.
- SUBDATE(date, interval_expression_unit): This function performs an arithmetic operation on the date passed as the first argument. The subdate() function will subtract the interval passed as the second argument from the date given as the first argument.
- ADDDATE(date, interval_expression_unit): This function performs an arithmetic operation on the date passed as the first argument. The adddate() function will add the interval passed as the second argument to the date given as the first argument.
READ MORE
- MySQL SELECT WHERE DATE IS TODAY
- MySQL SELECT WHERE
- MySQL WHERE DATE GREATER THAN
- MYSQL SELECT WHERE MONTH AND YEAR
- MySQL SELECT YESTERDAY’S DATE
- MySQL SELECT WHERE NOT IN
- MySQL SELECT WHERE LIKE
- MySQL SELECT WHERE NULL
We hope this article helped you with MySQL select where a date is between two dates. Good Luck!!!