This article will be looking into how to use greater than operator with a date. We will be going through a few examples to demonstrate the concept.

Table of Contents:

Let us get started by making the sample data to be used across the examples. Create a table named patient_appointment_details, followed by inserting some rows into it.

#create the table patient_appointment_details
CREATE TABLE patient_appointment_details (
patient_id INT,
patient_name VARCHAR(255),
appointment_date DATETIME
);
#insert rows into the table patient_appointment_details
INSERT INTO patient_appointment_details(patient_id,patient_name,appointment_date) 
VALUES(1,"Justin",'2021-08-6 19:13:30'),
(2,"Veronica",current_date()),
(3,"George",'2021-04-03 09:12:30'),
(4,"Veena",'2021-04-09 09:12:30'),
(5,"Tanya",'2021-04-08 09:12:30'),
(6,"Peter",'2021-04-04 07:07:20'),
(7,"Smitha",'2021-05-06 09:12:30'),
(8,"Walter",'2021-04-06 09:12:30'),
(9,"Daniel",'2021-04-10 09:12:30'),
(10,"Erick",adddate(curdate(), 20)),
(11,"Atharv",adddate(curdate(), 28)),
(12,"Eva",adddate(curdate(), 38));

To view the snapshot of the table patient_appointment_details, execute:

SELECT * FROM patient_appointment_details;

Output:-

image_1: patient_appointment_details

MySQL where date greater than today

Example: Get the details of patient appointment where appointment_date is after today

Observe the below query for the solution.

SELECT * FROM patient_appointment_details WHERE DATE(appointment_date) > CURDATE();

Output:-

image_2

MySQL where date greater than yesterday

Example2: Get the details of patient appointment where appointment_date is after yesterday

Observe the below query for the solution.

SELECT 
    *
FROM
    patient_appointment_details
WHERE
    DATE(appointment_date) > SUBDATE(CURRENT_DATE, 1);

Output:-

image_3

MySQL where date greater than 7 days

Example3: Get the details of patient appointment where appointment_date is after 7 days

Observe the below query for the solution.

SELECT 
    *
FROM
    patient_appointment_details
WHERE
    DATE(appointment_date) > ADDDATE(CURDATE(), 7);

Output:-

image_4

MySQL where date greater than 30 days ago

Example3: Get the details of patient appointment where appointment_date is after 30 days

Observe the below query for the solution.

SELECT 
    *
FROM
    patient_appointment_details
WHERE
    DATE(appointment_date) > ADDDATE(CURDATE(), 30);

Output:-

image_5

Here in the above queries we are using MySQL functions DATE(expression), CURDATE(), SUBDATE(date,interval) and ADDDATE(date,interval).

  • 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

We hope this article helped you with MySQL select where the date is greater than other dates. Good Luck!!!