Database Reference
In-Depth Information
Table 11.2
Continued
Function
Description
Date_Add()
Highly flexible date arithmetic function
Date_Format()
Returns a formatted date or time string
Day()
Returns the day portion of a date
DayOfWeek()
Returns the day of week for a date
Hour()
Returns the hour portion of a time
Minute()
Returns the minute portion of a time
Month()
Returns the month portion of a date
Now()
Returns the current date and time
Second()
Returns the second portion of a time
Time()
Returns the time portion of a date time
Year()
Returns the year portion of a date
This would be a good time to revisit data filtering using
WHERE
. Thus far we
have filtered data using
WHERE
clauses that compared numbers and text, but
frequently data needs to be filtered by date. Filtering by date requires some
extra care and the use of special MariaDB SQL functions.
The first thing to keep in mind is the date format used by MariaDB. Whenever
you specify a date, be it inserting or updating table values, or filtering using
WHERE
clauses, the date must be in the format
yyyy-mm-dd
. So, for September
1st, 2011, specify
2011-09-01
. Although other date formats might be recog-
nized, this is the preferred date format because it eliminates ambiguity (after all,
is 04/05/06 May 4th 2006, or April 5th 2006, or May 6th 2004, or… you get
the idea).
Tip
Always Use Four-Digit Years Two-digit years are supported, and MariaDB treats years
00-69
as
2000-2069
and
70-99
as
1970-1999
. While these might in fact be the
intended years, it is far safer to always use a full four-digit year so MariaDB does not
have to make any assumptions for you.
As such, a basic date comparison should be simple enough:
▼
Input
SELECT cust_id, order_num
FROM orders
WHERE order_date = '2011-09-01';
