Using Comparison Operators with Date and Time Functions
The following examples demonstrate how to use different comparison operators with date and time functions.
Consider a table Emp with the following values:
SELECT * FROM Emp;
+-------+------------+
| Name | DOJ |
+-------+------------+
| Jim | 2019-01-05 |
| Rob | 2020-01-04 |
| Peter | 2021-02-12 |
| Jack | 2019-03-20 |
| Mary | 2020-07-03 |
+-------+------------+To filter employees who joined in the month of January:
SELECT * FROM Emp WHERE MONTH(DOJ) = 01;
+------+------------+
| Name | DOJ |
+------+------------+
| Jim | 2019-01-05 |
| Rob | 2020-01-04 |
+------+------------+To filter employees who joined through the current date, assuming that the CURRENT_ is 2021-02-12:
SELECT * FROM Emp WHERE DOJ < CURRENT_DATE;+------+------------+| Name | DOJ |+------+------------+| Jim | 2019-01-05 || Rob | 2020-01-04 || Jack | 2019-03-20 || Mary | 2020-07-03 |+------+------------+
To filter employees who joined in a specific date range:
SELECT * FROM Emp WHERE DOJ BETWEEN DATE('2019-01-01') AND DATE('2020-01-30');
+------+------------+
| Name | DOJ |
+------+------------+
| Jim | 2019-01-05 |
| Rob | 2020-01-04 |
| Jack | 2019-03-20 |
+------+------------+Last modified: April 24, 2021