MariaDB Crash Course - Ben Forta [32]
Input
SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';
Output
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
Now try the same search using the Soundex() function to match all contact names that sound similar to Y. Lie:
Input
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
Output
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee |
+-------------+--------------+
Analysis
In this example, the WHERE clause uses the Soundex() function to convert both the cust_contact column value and the search string to their SOUNDEX values. Because Y. Lee and Y. Lie sound alike, their SOUNDEX values match, and so the WHERE clause correctly filtered the desired data.
Date and Time Manipulation Functions
Date and times are stored in tables using special datatypes using special internal formats so they may be sorted or filtered quickly and efficiently, as well as to save physical storage space.
The format used to store dates and times is usually of no use to your applications, and so date and time functions are almost always used to read, expand, and manipulate these values. Because of this, date and time manipulation functions are some of the most important functions in the MariaDB SQL language.
Table 11.2 lists some commonly used date and time manipulation functions.
Table 11.2 Commonly Used Date and Time Manipulation Functions
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 recognized, 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';
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