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