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