Database Reference
In-Depth Information
But one other type of date comparison warrants explanation. What if you
wanted to retrieve all orders placed in September 2011? A simple equality test
does not work as it matches the day of the month, too. There are several solu-
tions, one of which follows:
▼
Input
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2011-09-01' AND '2011-09-30';
▼
Output
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+---------+-----------+
▼
Analysis
Here a
BETWEEN
operator is used to define
2011-09-01
and
2011-09-30
as
the range of dates to match.
Here's another solution (one that won't require you to remember how many
days are in each month, or worry about February in leap years):
▼
Input
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2011 AND Month(order_date) = 9;
▼
Analysis
Year()
is a function that returns the year from a date (or a date time).
Similarly,
Month()
returns the month from a date.
WHERE Year(order_
date) = 2011 AND Month(order_date) = 9
thus retrieves all rows that
have an
order_date
in year
2011
and in month
9
.
Note
Support For Microseconds MariaDB 5.3 adds support for microseconds when working
with date and time values.
