Fetch student records whose result declared 12 days before the current date in MYSQL

MySQLMySQLi Database

For this, you need to compare and find the difference between the current date and the result date of students. This can be done with AND operator along with DATEDIFF().

Let us first create a table −

mysql> create table DemoTable1547
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20),
   -> StudentMarks int,
   -> StudentResultDeclareDate datetime
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1547(StudentName,StudentMarks,StudentResultDeclareDate) values('Chris',56,'2019-10-13 13:00:00')
   -> ;
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1547(StudentName,StudentMarks,StudentResultDeclareDate) values('Bob',60,'2019-10-13 12:00:00');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1547(StudentName,StudentMarks,StudentResultDeclareDate) values('Mike',45,'2019-10-13 14:00:00');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1547(StudentName,StudentMarks,StudentResultDeclareDate) values('Carol',78,'2019-10-01 14:00:00');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1547;

This will produce the following output −

+-----------+-------------+--------------+--------------------------+
| StudentId | StudentName | StudentMarks | StudentResultDeclareDate |
+-----------+-------------+--------------+--------------------------+
|         1 | Chris       |           56 | 2019-10-13 13:00:00      |
|         2 | Bob         |           60 | 2019-10-13 12:00:00      |
|         3 | Mike        |           45 | 2019-10-13 14:00:00      |
|         4 | Carol       |           78 | 2019-10-01 14:00:00      |
+-----------+-------------+--------------+--------------------------+
4 rows in set (0.00 sec)

The current date is as follows −

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-10-13 |
+------------+
1 row in set (0.00 sec)

Following is the query to fetch student records whose result declared 12 days before the current date −

mysql> select * from DemoTable1547 where datediff(curdate(),StudentResultDeclareDate) >=12 and StudentMarks > 50;

This will produce the following output −

+-----------+-------------+--------------+--------------------------+
| StudentId | StudentName | StudentMarks | StudentResultDeclareDate |
+-----------+-------------+--------------+--------------------------+
|         4 | Carol       | 7          8 | 2019-10-01 14:00:00      |
+-----------+-------------+--------------+--------------------------+
1 row in set (0.00 sec)
raja
Published on 12-Dec-2019 10:43:38
Advertisements