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:
- MySQL where date greater than today
- MySQL where date greater than yesterday
- MySQL where date greater than 7 days
- MySQL where date greater than 30 days ago
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:-

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.
Frequently Asked:
- MYSQL: CREATE A DATABASE
- 3 ways to skip first 10 results
- DROP multiple columns in MySQL with single ALTER statement
- Trigger with if condition in mysql
SELECT * FROM patient_appointment_details WHERE DATE(appointment_date) > CURDATE();
Output:-

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

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

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

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
- MySQL SELECT WHERE DATE IS TODAY
- MySQL SELECT WHERE
- 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 the date is greater than other dates. Good Luck!!!