MySQL WHERE DATE BETWEEN

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.

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!!!