Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

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:

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?