Greater Than Date in MySQL

1. Use the INSERT Statement to Insert Entries in a Table in MySQL
2. Fetch Data Greater Than a Date in MySQL

In this tutorial, we aim at exploring the concept of finding entries in a table in MySQL based on a condition associated with dates.

Most businesses and organizations that use MySQL for data analysis or visualization need to sort or fetch different table values of their users based on the date of entry or expiry or something else altogether.

We can do this efficiently in MySQL using the INSERT statement. Using this statement, we can insert the dates, use the SORT BY statement, and arrange the values of the table in any way we want.

For example, for a product-based company, if the analyst wishes to sort the records of different users based on their date of registration on the platform, they can use the ORDER BY statement to get this done.

However, sometimes, the data that needs to be fetched is greater than or less than a particular date. For example, a product-based company might require to find entries of products that have expiry after a particular date.

In this case, the analyst must find entries based on a greater value date than another date under consideration. This operation can be performed in MySQL using the WHERE clause.

Let us try to understand this statement in greater depth.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_dates_3, along with a few rows.

-- create the table student_dates_3
CREATE TABLE student_dates_3(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_date date,
  primary key(stu_id)
);

Use the INSERT Statement to Insert Entries in a Table in MySQL

The above query creates a table with the name student_dates_3. With the INSERT statement’s help, let us add data for a few students.

This operation can be done as follows.

-- insert rows to the table student_dates_3
INSERT INTO student_dates_3(stu_id,stu_firstName,stu_date)
 VALUES(1,"Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
 (2,"Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
 (3,"Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
 (4,"Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
 (5,"Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y')),
 (6,"Rutvik",STR_TO_DATE('16-January-2001', '%d-%M-%Y'));

The code would enter the student data in the table student_dates_3. We can visualize this table with the following command.

SELECT * from student_dates_3;

The stated code block would generate the following output.

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19
6 		Rutvik			2001-01-16

As shown in the above table, we have successfully entered dates in our table student_dates_3.

Let us filter these dates based on a particular condition associated with a date.

Fetch Data Greater Than a Date in MySQL

One needs to use the WHERE clause to get this done. This clause is generally used to filter data to make the query more succinct and time-bound.

To fetch data based on a given date, let us consider 16th January 2001, for example. Let us try to fetch data of the students with stu_date greater than the date.

We can do this operation with the help of the following query.

SELECT * from student_dates_3 WHERE stu_date > '2001-01-16';

As we can see here, we are using the WHERE clause and comparing the dates of each student with the date 2001-01-16. This query should fetch all the students with stu_date greater than this date.

Let us check out the output of the query.

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

As we can see here, the last entry has been filtered. All the students with dates greater than the date under consideration have been kept, and others have been filtered out.

Therefore, with the help of the WHERE clause and the INSERT statement to add the dates, we can efficiently find values in a table in MySQL with values greater than a specified date.

Some other related topics that can help learn the concept better are below.

• WHERE clause in MySQL.
• INSERT function in MySQL (to insert dates into a table).