JIYIK CN >

In this article, we aim to explore the concept of finding entries in a table in MySQL based on conditions associated with dates.

Most of the businesses and organizations using MySQL for data analysis or visualization need to sort or fetch different table values ​​from the user based on entry date or expiration date or something else.

We can INSERTdo this efficiently in MySQL using the INSERT statement. Using this statement, we can insert the date, use SORT BYthe INSERT statement, and arrange the values ​​of the table in any way we want.

For example, for a product-based company, if analysts want to sort the records of different users based on their registration date on the platform, they can use ORDER BYthe statement to do this.

Sometimes, however, you need to retrieve data that is greater or less than a specific date. For example, a product-based company might need to find product entries that expire after a certain date.

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

Let us try to understand this sentence more deeply.

However, before we get started, let's create a dummy dataset to work with. Here, we create a table student_dates_3and 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 INSERTthe statement to insert entries into the MySQL table

The above query creates a student_dates_3table named . With the help of INSERT statement, let us add data for a few students.

This operation can be performed 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'));

This code will student_dates_3enter the student data in the table . We can visualize this table using the following command.

SELECT * from student_dates_3;

The stated code block will 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 table above, we have successfully student_dates_3entered the date in the table.

Let's filter these dates based on certain criteria related to the dates.

Get data greater than date in MySQL

You need to use WHEREthe clause to complete this operation. This clause is generally used to filter data to make the query more concise and time-limited.

For example, to get data based on a given date, let us consider 16th January 2001. Let us try to get stu_datethe data of students whose is greater than the date.

We can do this 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 clause and comparing WHEREeach student's date with the date . This query should fetch all students whose is greater than this date.2001-01-16stu_date

Let's check 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 whose date is greater than the considered date are retained and the others are filtered out.

Therefore, with the help of WHEREthe clause and INSERTstatement to add the date, we can effectively find the values ​​greater than the specified date in the MySQL table.

Here are some other related topics that can help in learning the concept better.

• WHEREClause in MySQL .
• Function in MySQL INSERT(Insert date into table).