Using Comparison Operators with Date and Time Functions

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

The following examples demonstrate how to use different comparison operators with date and time functions.

Consider a table Emp with the following values:

SQL

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:

SQL

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:

SQL

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:

SQL

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

Using Comparison Operators with Date and Time Functions

Was this article helpful?

Was this article helpful?