ใช้ DATEDIFF() คำนวณความต่างวัน ร่วมกับ CURDATE() เพื่อกรองข้อมูล
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)
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)
mysql> SELECT * FROM DemoTable1547;
+-----------+-------------+--------------+--------------------------+ | 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)
วันที่ปัจจุบัน (สมมติ):
mysql> SELECT CURDATE();
+------------+ | CURDATE() | +------------+ | 2019-10-13 | +------------+ 1 row in set (0.00 sec)
คิวรี่หาผลประกาศ ≥ 12 วัน และคะแนน > 50:
mysql> SELECT * FROM DemoTable1547
-> WHERE DATEDIFF(CURDATE(), StudentResultDeclareDate) >= 12
-> AND StudentMarks > 50;
+-----------+-------------+--------------+--------------------------+ | StudentId | StudentName | StudentMarks | StudentResultDeclareDate | +-----------+-------------+--------------+--------------------------+ | 4 | Carol | 78 | 2019-10-01 14:00:00 | +-----------+-------------+--------------+--------------------------+ 1 row in set (0.00 sec)
หมายเหตุ: DATEDIFF คืนค่าจำนวนวัน (date1 - date2) ใช้กับ DATE หรือ DATETIME ได้ (ตัดเวลาทิ้ง)
