Database Reference
In-Depth Information
can select rows by looking for values that occur before or after a given date, within a
date range, or that match particular month or day values.
Comparing dates to one another
The following statements find rows from the
date_val
table that occur either before
1900 or during the 1900s:
mysql>
SELECT d FROM date_val where d < '1900-01-01';
+------------+
| d |
+------------+
| 1864-02-28 |
+------------+
mysql>
SELECT d FROM date_val where d BETWEEN '1900-01-01' AND '1999-12-31';
+------------+
| d |
+------------+
| 1900-01-15 |
| 1999-12-31 |
+------------+
When you don't know the exact date needed for a comparison in a
WHERE
clause, you
can often calculate it using an expression. For example, to perform an “on this day in
history” statement to search for rows in a table named
history
to find events occurring
exactly 50 years ago, do this:
SELECT
*
FROM
history
WHERE
d
=
DATE_SUB
(
CURDATE
(),
INTERVAL
50
YEAR
);
You see this kind of thing in newspapers that run columns showing what the news events
were in times past. (In essence, the statement identifies those events that have reached
their
n
-th anniversary.) To retrieve events that occurred “on this day” for any year rather
than “on this date” for a specific year, the statement is a bit different. In that case, you
need to find rows that match the current calendar day, ignoring the year. That topic is
discussed in
“Comparing dates to calendar days” on page 231
.
Calculated dates are useful for range testing as well. For example, to find dates that occur
later than 20 years ago, use
DATE_SUB()
to calculate the cutoff date:
mysql>
SELECT d FROM date_val WHERE d >= DATE_SUB(CURDATE(),INTERVAL 20 YEAR);
+------------+
| d |
+------------+
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
+------------+
Note that the expression in the
WHERE
clause isolates the date column
d
on one side of
the comparison operator. This is usually a good idea; if the column is indexed, placing
it alone on one side of a comparison enables MySQL to process the statement more
