SingleStore DB

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_DATE 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 |
+------+------------+