Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

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 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

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?