Database Reference
In-Depth Information
▼
Output
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
+---------+-----------+
▼
Analysis
That
SELECT
statement worked; it retrieved a single order record, one with an
order_date
of
2011-09-01
.
But is using
WHERE order_date = '2011-09-01'
safe?
order_date
has
a datatype of
datetime
. This type stores dates along with time values. The values
in our example tables all have times of
00:00:00
, but that might not always
be the case. What if order dates were stored using the current date and time (so
you'd not only know the order date but also the time of day that the order was
placed)? Then
WHERE order_date = '2011-09-01'
fails if, for example,
the stored
order_date
value is
2011-09-01 11:30:05
. Even though a row
with that date is present, it is not retrieved because the
WHERE
match failed.
The solution is to instruct MariaDB to only compare the specified date to the
date portion of the column instead of using the entire column value. To do this
you must use the
Date()
function.
Date(order_date)
instructs MariaDB
to extract just the date part of the column, and so a safer
SELECT
statement is
▼
Input
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2011-09-01';
Tip
If You Mean Date Use
Date()
It's a good practice to use
Date()
if what you
want is just the date, even if you know that the column only contains dates. This way, if
somehow a date time value ends up in the table in the future, your SQL won't break. Oh,
and yes, there is a
Time()
function, too, and it should be used when you want
the time.
Now that you know how to use dates to test for equality, using all the
other operators (introduced in Chapter 6, “Filtering Data”) should be self-
explanatory.
