Including single quotes

Since strings are demarcated by single quotes, you will need to be alert for strings that include single quotes or apostrophes. For example, you won’t be able to insert the following string because the server will think that the apostrophe in the word doesn’t marks the end of the string:

UPDATE string_tbl
SET text_fld = 'This string doesn't work';

To make the server ignore the apostrophe in the word doesn’t, you will need to add an escape to the string so that the server treats the apostrophe like any other character in the string. All three servers allow you to escape a single quote by adding another single quote directly before, as in:

mysql> UPDATE string_tbl
    -> SET text_fld = 'This string didn''t work, but it does now';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE string_tbl SET text_fld =
  'This string didn't work, but it does now'

If you retrieve a string for use in a screen or report field, you don’t need to do anything special to handle embedded quotes:

mysql> SELECT text_fld
    -> FROM string_tbl;
+------------------------------------------+
| text_fld                                 |
+------------------------------------------+
| This string didn't work, but it does now |
+------------------------------------------+
1 row in set (0.00 sec)

However, if you are retrieving the string to add to a file that another program will read, you may want to include the escape as part of the retrieved string. If you are using MySQL, you can use the built-in function quote(), which places quotes around the entire string and adds escapes to any single quotes/apostrophes within the string. Here’s what our string looks like when retrieved via the quote() function:

mysql> SELECT quote(text_fld)
    -> FROM string_tbl;
+---------------------------------------------+
| QUOTE(text_fld)                             |
+---------------------------------------------+
| 'This string didn't work, but it does now' |
+---------------------------------------------+
1 row in set (0.04 sec)

When retrieving data for data export, you may want to use the quote() function for all non-system-generated character columns, such as a customer_notes column.

Including special characters

If your application is multinational in scope, you might find yourself working with strings that include characters that do not appear on your keyboard. When working with the French and German languages, for example, you might need to include accented characters such as é and ö. The SQL Server and MySQL servers include the built-in function char() so that you can build strings from any of the 255 characters in the ASCII character set (Oracle Database users can use the chr() function). To demonstrate, the next example retrieves a typed string and its equivalent built via individual characters:

mysql> SELECT 'abcdefg', CHAR(97,98,99,100,101,102,103);
+---------+--------------------------------+
| abcdefg | CHAR(97,98,99,100,101,102,103) |
+---------+--------------------------------+
| abcdefg | abcdefg                        |
+---------+--------------------------------+
1 row in set (0.01 sec)

Thus, the 97^th character in the ASCII character set is the letter a. While the characters shown in the preceding example are not special, the following examples show the location of the accented characters along with other special characters, such as currency symbols:

mysql> SELECT CHAR(128,129,130,131,132,133,134,135,136,137);
+-----------------------------------------------+
| CHAR(128,129,130,131,132,133,134,135,136,137) |
+-----------------------------------------------+
| Çüéâäàåçêë                                    |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CHAR(138,139,140,141,142,143,144,145,146,147);
+-----------------------------------------------+
| CHAR(138,139,140,141,142,143,144,145,146,147) |
+-----------------------------------------------+
| èïîìÄÅÉæÆô                                    |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CHAR(148,149,150,151,152,153,154,155,156,157);
+-----------------------------------------------+
| CHAR(148,149,150,151,152,153,154,155,156,157) |
+-----------------------------------------------+
| öòûùÿÖÜø£Ø                                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(158,159,160,161,162,163,164,165);
+---------------------------------------+
| CHAR(158,159,160,161,162,163,164,165) |
+---------------------------------------+
| ׃áíóúñÑ                              |
+---------------------------------------+
1 row in set (0.01 sec)

Building strings character by character can be quite tedious, especially if only a few of the characters in the string are accented. Fortunately, you can use the concat() function to concatenate individual strings, some of which you can type while others you can generate via the char() function. For example, the following shows how to build the phrase danke schön using the concat() and char() functions:

mysql> SELECT CONCAT('danke sch', CHAR(148), 'n');
+-------------------------------------+
| CONCAT('danke sch', CHAR(148), 'n') |
+-------------------------------------+
| danke schön                         |
+-------------------------------------+
1 row in set (0.00 sec)

SELECT 'danke sch' || CHR(148) || 'n'
FROM dual;

SELECT 'danke sch' + CHAR(148) + 'n'

If you have a character and need to find its ASCII equivalent, you can use the ascii() function, which takes the leftmost character in the string and returns a number:

mysql> SELECT ASCII('ö');
+------------+
| ASCII('ö') |
+------------+
|        148 |
+------------+
1 row in set (0.00 sec)

Using the char(), ascii(), and concat() functions (or concatenation operators), you should be able to work with any Roman language even if you are using a keyboard that does not include accented or special characters.

String functions that return numbers

Of the string functions that return numbers, one of the most commonly used is the length() function, which returns the number of characters in the string (SQL Server users will need to use the len() function). The following query applies the length() function to each column in the string_tbl table:

mysql> SELECT LENGTH(char_fld) char_length,
    ->   LENGTH(vchar_fld) varchar_length,
    ->   LENGTH(text_fld) text_length
    -> FROM string_tbl;
+-------------+----------------+-------------+
| char_length | varchar_length | text_length |
+-------------+----------------+-------------+
|          28 |             28 |          28 |
+-------------+----------------+-------------+
1 row in set (0.00 sec)

While the lengths of the varchar and text columns are as expected, you might have expected the length of the char column to be 30, since I told you that strings stored in char columns are right-padded with spaces. The MySQL server removes trailing spaces from char data when it is retrieved, however, so you will see the same results from all string functions regardless of the type of column in which the strings are stored.

Along with finding the length of a string, you might want to find the location of a substring within a string. For example, if you want to find the position at which the string 'characters' appears in the vchar_fld column, you could use the position() function, as demonstrated by the following:

mysql> SELECT POSITION('characters' IN vchar_fld)
    -> FROM string_tbl;
+-------------------------------------+
| POSITION('characters' IN vchar_fld) |
+-------------------------------------+
|                                  19 |
+-------------------------------------+
1 row in set (0.12 sec)

If the substring cannot be found, the position() function returns 0.

If you want to start your search at something other than the first character of your target string, you will need to use the locate() function, which is similar to the position() function except that it allows an optional third parameter, which is used to define the search’s start position. The locate() function is also proprietary, whereas the position() function is part of the SQL:2003 standard. Here’s an example asking for the position of the string 'is' starting at the fifth character in the vchar_fld column:

mysql> SELECT LOCATE('is', vchar_fld, 5)
    -> FROM string_tbl;
+----------------------------+
| LOCATE('is', vchar_fld, 5) |
+----------------------------+
|                         13 |
+----------------------------+
1 row in set (0.02 sec)

Another function that takes strings as arguments and returns numbers is the string comparison function strcmp(). Strcmp(), which is implemented only by MySQL and has no analog in Oracle Database or SQL Server, takes two strings as arguments, and returns one of the following:

• −1 if the first string comes before the second string in sort order

• 0 if the strings are identical

• 1 if the first string comes after the second string in sort order

To illustrate how the function works, I first show the sort order of five strings using a query, and then show how the strings compare to one another using strcmp(). Here are the five strings that I insert into the string_tbl table:

mysql> DELETE FROM string_tbl;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO string_tbl(vchar_fld)
    -> VALUES ('abcd'),
    ->        ('xyz'),
    ->        ('QRSTUV'),
    ->        ('qrstuv'),
    ->        ('12345');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

Here are the five strings in their sort order:

mysql> SELECT vchar_fld
    -> FROM string_tbl
    -> ORDER BY vchar_fld;
+-----------+
| vchar_fld |
+-----------+
| 12345     |
| abcd      |
| QRSTUV    |
| qrstuv    |
| xyz       |
+-----------+
5 rows in set (0.00 sec)

The next query makes six comparisons among the five different strings:

mysql> SELECT STRCMP('12345','12345') 12345_12345,
    ->   STRCMP('abcd','xyz') abcd_xyz,
    ->   STRCMP('abcd','QRSTUV') abcd_QRSTUV,
    ->   STRCMP('qrstuv','QRSTUV') qrstuv_QRSTUV,
    ->   STRCMP('12345','xyz') 12345_xyz,
    ->   STRCMP('xyz','qrstuv') xyz_qrstuv;
+-------------+----------+-------------+---------------+-----------+------------+
| 12345_12345 | abcd_xyz | abcd_QRSTUV | qrstuv_QRSTUV | 12345_xyz | xyz_qrstuv |
+-------------+----------+-------------+---------------+-----------+------------+
|           0 |       −1 |          −1 |             0 |        −1 |          1 |
+-------------+----------+-------------+---------------+-----------+------------+
1 row in set (0.00 sec)

The first comparison yields 0, which is to be expected since I compared a string to itself. The fourth comparison also yields 0, which is a bit surprising, since the strings are composed of the same letters, with one string all uppercase and the other all lowercase. The reason for this result is that MySQL’s strcmp() function is case-insensitive, which is something to remember when using the function. The other four comparisons yield either −1 or 1 depending on whether the first string comes before or after the second string in sort order. For example, strcmp('abcd','xyz') yields −1, since the string 'abcd' comes before the string 'xyz'.

Along with the strcmp() function, MySQL also allows you to use the like and regexp operators to compare strings in the select clause. Such comparisons will yield 1 (for true) or 0 (for false). Therefore, these operators allow you to build expressions that return a number, much like the functions described in this section. Here’s an example using like:

mysql> SELECT name, name LIKE '%y' ends_in_y
    -> FROM category;
+-------------+-----------+
| name        | ends_in_y |
+-------------+-----------+
| Action      |         0 |
| Animation   |         0 |
| Children    |         0 |
| Classics    |         0 |
| Comedy      |         1 |
| Documentary |         1 |
| Drama       |         0 |
| Family      |         1 |
| Foreign     |         0 |
| Games       |         0 |
| Horror      |         0 |
| Music       |         0 |
| New         |         0 |
| Sci-Fi      |         0 |
| Sports      |         0 |
| Travel      |         0 |
+-------------+-----------+
16 rows in set (0.00 sec)

This example retrieves all the category names, along with an expression that returns 1 if the name ends in “y” or 0 otherwise. If you want to perform more complex pattern matches, you can use the regexp operator, as demonstrated by the following:

mysql> SELECT name, name REGEXP 'y$' ends_in_y
    -> FROM category;
+-------------+-----------+
| name        | ends_in_y |
+-------------+-----------+
| Action      |         0 |
| Animation   |         0 |
| Children    |         0 |
| Classics    |         0 |
| Comedy      |         1 |
| Documentary |         1 |
| Drama       |         0 |
| Family      |         1 |
| Foreign     |         0 |
| Games       |         0 |
| Horror      |         0 |
| Music       |         0 |
| New         |         0 |
| Sci-Fi      |         0 |
| Sports      |         0 |
| Travel      |         0 |
+-------------+-----------+
16 rows in set (0.00 sec)

The second column of this query returns 1 if the value stored in the name column matches the given regular expression.

String functions that return strings

In some cases, you will need to modify existing strings, either by extracting part of the string or by adding additional text to the string. Every database server includes multiple functions to help with these tasks. Before I begin, I once again reset the data in the string_tbl table:

mysql> DELETE FROM string_tbl;
Query OK, 5 rows affected (0.00 sec)

mysql> INSERT INTO string_tbl (text_fld)
    -> VALUES ('This string was 29 characters');
Query OK, 1 row affected (0.01 sec)

Earlier in the chapter, I demonstrated the use of the concat() function to help build words that include accented characters. The concat() function is useful in many other situations, including when you need to append additional characters to a stored string. For instance, the following example modifies the string stored in the text_fld column by tacking an additional phrase on the end:

mysql> UPDATE string_tbl
    -> SET text_fld = CONCAT(text_fld, ', but now it is longer');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The contents of the text_fld column are now as follows:

mysql> SELECT text_fld
    -> FROM string_tbl;
+-----------------------------------------------------+
| text_fld                                            |
+-----------------------------------------------------+
| This string was 29 characters, but now it is longer |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Thus, like all functions that return a string, you can use concat() to replace the data stored in a character column.

Another common use for the concat() function is to build a string from individual pieces of data. For example, the following query generates a narrative string for each customer:

mysql> SELECT concat(first_name, ' ', last_name,
    ->   ' has been a customer since ', date(create_date)) cust_narrative
    -> FROM customer;
+---------------------------------------------------------+
| cust_narrative                                          |
+---------------------------------------------------------+
| MARY SMITH has been a customer since 2006-02-14         |
| PATRICIA JOHNSON has been a customer since 2006-02-14   |
| LINDA WILLIAMS has been a customer since 2006-02-14     |
| BARBARA JONES has been a customer since 2006-02-14      |
| ELIZABETH BROWN has been a customer since 2006-02-14    |
| JENNIFER DAVIS has been a customer since 2006-02-14     |
| MARIA MILLER has been a customer since 2006-02-14       |
| SUSAN WILSON has been a customer since 2006-02-14       |
| MARGARET MOORE has been a customer since 2006-02-14     |
| DOROTHY TAYLOR has been a customer since 2006-02-14     |
...
| RENE MCALISTER has been a customer since 2006-02-14     |
| EDUARDO HIATT has been a customer since 2006-02-14      |
| TERRENCE GUNDERSON has been a customer since 2006-02-14 |
| ENRIQUE FORSYTHE has been a customer since 2006-02-14   |
| FREDDIE DUGGAN has been a customer since 2006-02-14     |
| WADE DELVALLE has been a customer since 2006-02-14      |
| AUSTIN CINTRON has been a customer since 2006-02-14     |
+---------------------------------------------------------+
599 rows in set (0.00 sec)

The concat() function can handle any expression that returns a string, and will even convert numbers and dates to string format, as evidenced by the date column (create_date) used as an argument. Although Oracle Database includes the concat() function, it will accept only two string arguments, so the previous query will not work on Oracle. Instead, you would need to use the concatenation operator (||) rather than a function call, as in:

SELECT first_name || ' ' || last_name ||
  ' has been a customer since ' || date(create_date)) cust_narrative
FROM customer;

SQL Server does not include a concat() function, so you would need to use the same approach as the previous query, except that you would use SQL Server’s concatenation operator (+) instead of ||.

While concat() is useful for adding characters to the beginning or end of a string, you may also have a need to add or replace characters in the middle of a string. All three database servers provide functions for this purpose, but all of them are different, so I demonstrate the MySQL function and then show the functions from the other two servers.

MySQL includes the insert() function, which takes four arguments: the original string, the position at which to start, the number of characters to replace, and the replacement string. Depending on the value of the third argument, the function may be used to either insert or replace characters in a string. With a value of 0 for the third argument, the replacement string is inserted and any trailing characters are pushed to the right, as in:

mysql> SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;
+---------------------+
| string              |
+---------------------+
| goodbye cruel world |
+---------------------+
1 row in set (0.00 sec)

In this example, all characters starting from position 9 are pushed to the right and the string 'cruel' is inserted. If the third argument is greater than zero, then that number of characters is replaced with the replacement string, as in:

mysql> SELECT INSERT('goodbye world', 1, 7, 'hello') string;
+-------------+
| string      |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)

For this example, the first seven characters are replaced with the string 'hello'. Oracle Database does not provide a single function with the flexibility of MySQL’s insert() function, but Oracle does provide the replace() function, which is useful for replacing one substring with another. Here’s the previous example reworked to use replace():

SELECT REPLACE('goodbye world', 'goodbye', 'hello')
FROM dual;

All instances of the string 'goodbye' will be replaced with the string 'hello', resulting in the string 'hello world'. The replace() function will replace every instance of the search string with the replacement string, so you need to be careful that you don’t end up with more replacements than you anticipated.

SQL Server also includes a replace() function with the same functionality as Oracle’s, but SQL Server also includes a function called stuff() with similar functionality to MySQL’s insert() function. Here’s an example:

SELECT STUFF('hello world', 1, 5, 'goodbye cruel')

When executed, five characters are removed starting at position 1, and then the string 'goodbye cruel' is inserted at the starting position, resulting in the string 'goodbye cruel world'.

Along with inserting characters into a string, you may have a need to extract a substring from a string. For this purpose, all three servers include the substring() function (although Oracle Database’s version is called substr()), which extracts a specified number of characters starting at a specified position. The following example extracts five characters from a string starting at the ninth position:

mysql> SELECT SUBSTRING('goodbye cruel world', 9, 5);
+----------------------------------------+
| SUBSTRING('goodbye cruel world', 9, 5) |
+----------------------------------------+
| cruel                                  |
+----------------------------------------+
1 row in set (0.00 sec)

Along with the functions demonstrated here, all three servers include many more built-in functions for manipulating string data. While many of them are designed for very specific purposes, such as generating the string equivalent of octal or hexadecimal numbers, there are many other general-purpose functions as well, such as functions that remove or add trailing spaces. For more information, consult your server’s SQL reference guide, or a general-purpose SQL reference guide such as SQL in a Nutshell (O’Reilly).

String representations of temporal data

Table 2-4 in Chapter 2 presented the more popular date components; to refresh your memory, Table 7-2 shows these same components.

To build a string that the server can interpret as a date, datetime, or time, you need to put the various components together in the order shown in Table 7-3.

Thus, to populate a datetime column with 3:30 P.M. on September 17, 2019, you will need to build the following string:

'2019-09-17 15:30:00'

If the server is expecting a datetime value, such as when updating a datetime column or when calling a built-in function that takes a datetime argument, you can provide a properly formatted string with the required date components, and the server will do the conversion for you. For example, here’s a statement used to modify the return date of a film rental:

UPDATE rental
SET return_date = '2019-09-17 15:30:00'
WHERE rental_id = 99999;

The server determines that the string provided in the set clause must be a datetime value, since the string is being used to populate a datetime column. Therefore, the server will attempt to convert the string for you by parsing the string into the six components (year, month, day, hour, minute, second) included in the default datetime format.

String-to-date conversions

If the server is not expecting a datetime value, or if you would like to represent the datetime using a nondefault format, you will need to tell the server to convert the string to a datetime. For example, here is a simple query that returns a datetime value using the cast() function:

mysql> SELECT CAST('2019-09-17 15:30:00' AS DATETIME);
+-----------------------------------------+
| CAST('2019-09-17 15:30:00' AS DATETIME) |
+-----------------------------------------+
| 2019-09-17 15:30:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

We cover the cast() function at the end of this chapter. While this example demonstrates how to build datetime values, the same logic applies to the date and time types as well. The following query uses the cast() function to generate a date value and a time value:

mysql> SELECT CAST('2019-09-17' AS DATE) date_field,
    ->   CAST('108:17:57' AS TIME) time_field;
+------------+------------+
| date_field | time_field |
+------------+------------+
| 2019-09-17 | 108:17:57  |
+------------+------------+
1 row in set (0.00 sec)

You may, of course, explicitly convert your strings even when the server is expecting a date, datetime, or time value, rather than letting the server do an implicit conversion.

When strings are converted to temporal values—whether explicitly or implicitly—you must provide all the date components in the required order. While some servers are quite strict regarding the date format, the MySQL server is quite lenient about the separators used between the components. For example, MySQL will accept all of the following strings as valid representations of 3:30 P.M. on September 17, 2019:

'2019-09-17 15:30:00'
'2019/09/17 15:30:00'
'2019,09,17,15,30,00'
'20190917153000'

Although this gives you a bit more flexibility, you may find yourself trying to generate a temporal value without the default date components; the next section demonstrates a built-in function that is far more flexible than the cast() function.

Functions for generating dates

If you need to generate temporal data from a string, and the string is not in the proper form to use the cast() function, you can use a built-in function that allows you to provide a format string along with the date string. MySQL includes the str_to_date() function for this purpose. Say, for example, that you pull the string 'September 17, 2019' from a file and need to use it to update a date column. Since the string is not in the required YYYY-MM-DD format, you can use str_to_date() instead of reformatting the string so that you can use the cast() function, as in:

UPDATE rental
SET return_date = STR_TO_DATE('September 17, 2019', '%M %d, %Y')
WHERE rental_id = 99999;

The second argument in the call to str_to_date() defines the format of the date string, with, in this case, a month name (%M), a numeric day (%d), and a four-digit numeric year (%Y). While there are over 30 recognized format components, Table 7-4 defines the dozen or so most commonly used components.

The str_to_date() function returns a datetime, date, or time value depending on the contents of the format string. For example, if the format string includes only %H, %i, and %s, then a time value will be returned.

If you are trying to generate the current date/time, then you won’t need to build a string, because the following built-in functions will access the system clock and return the current date and/or time as a string for you:

mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2019-06-05     | 16:54:36       | 2019-06-05 16:54:36 |
+----------------+----------------+---------------------+
1 row in set (0.12 sec)

The values returned by these functions are in the default format for the temporal type being returned. Oracle Database includes current_date() and current_timestamp() but not current_time(), and SQL Server includes only the current_timestamp() function.

Temporal functions that return dates

Many of the built-in temporal functions take one date as an argument and return another date. MySQL’s date_add() function, for example, allows you to add any kind of interval (e.g., days, months, years) to a specified date to generate another date. Here’s an example that demonstrates how to add five days to the current date:

mysql> SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY);
+------------------------------------------+
| DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY) |
+------------------------------------------+
| 2019-06-10                               |
+------------------------------------------+
1 row in set (0.06 sec)

The second argument is composed of three elements: the interval keyword, the desired quantity, and the type of interval. Table 7-5 shows some of the commonly used interval types.

While the first six types listed in Table 7-5 are pretty straightforward, the last three types require a bit more explanation since they have multiple elements. For example, if you are told that a film was actually returned 3 hours, 27 minutes, and 11 seconds later than what was originally specified, you can fix it via the following:

UPDATE rental
SET return_date = DATE_ADD(return_date, INTERVAL '3:27:11' HOUR_SECOND)
WHERE rental_id = 99999;

In this example, the date_add() function takes the value in the return_date column, adds 3 hours, 27 minutes, and 11 seconds to it, and uses the value that results to modify the return_date column.

Or, if you work in HR and found out that employee ID 4789 claimed to be older than he actually is, you could add 9 years and 11 months to his birth date, as in:

UPDATE employee
SET birth_date = DATE_ADD(birth_date, INTERVAL '9-11' YEAR_MONTH)
WHERE emp_id = 4789;

UPDATE employee
SET birth_date =
  DATEADD(MONTH, 119, birth_date)
WHERE emp_id = 4789

UPDATE employee
SET birth_date = ADD_MONTHS(birth_date, 119)
WHERE emp_id = 4789;

There are some cases where you want to add an interval to a date, and you know where you want to arrive but not how many days it takes to get there. For example, let’s say that a bank customer logs on to the online banking system and schedules a transfer for the end of the month. Rather than writing some code that figures out the current month and then looks up the number of days in that month, you can call the last_day() function, which does the work for you (both MySQL and Oracle Database include the last_day() function; SQL Server has no comparable function). If the customer asks for the transfer on September 17, 2019, you could find the last day of September via the following:

mysql> SELECT LAST_DAY('2019-09-17');
+------------------------+
| LAST_DAY('2019-09-17') |
+------------------------+
| 2019-09-30             |
+------------------------+
1 row in set (0.10 sec)

Whether you provide a date or datetime value, the last_day() function always returns a date. Although this function may not seem like an enormous timesaver, the underlying logic can be tricky if you’re trying to find the last day of February and need to figure out whether the current year is a leap year.

Temporal functions that return strings

Most of the temporal functions that return string values are used to extract a portion of a date or time. For example, MySQL includes the dayname() function to determine which day of the week a certain date falls on, as in:

mysql> SELECT DAYNAME('2019-09-18');
+-----------------------+
| DAYNAME('2019-09-18') |
+-----------------------+
| Wednesday             |
+-----------------------+
1 row in set (0.00 sec)

Many such functions are included with MySQL for extracting information from date values, but I recommend that you use the extract() function instead, since it’s easier to remember a few variations of one function than to remember a dozen different functions. Additionally, the extract() function is part of the SQL:2003 standard and has been implemented by Oracle Database as well as MySQL.

The extract() function uses the same interval types as the date_add() function (see Table 7-5) to define which element of the date interests you. For example, if you want to extract just the year portion of a datetime value, you can do the following:

mysql> SELECT EXTRACT(YEAR FROM '2019-09-18 22:19:05');
+------------------------------------------+
| EXTRACT(YEAR FROM '2019-09-18 22:19:05') |
+------------------------------------------+
|                                     2019 |
+------------------------------------------+
1 row in set (0.00 sec)

SELECT DATEPART(YEAR, GETDATE())

Temporal functions that return numbers

Earlier in this chapter, I showed you a function used to add a given interval to a date value, thus generating another date value. Another common activity when working with dates is to take two date values and determine the number of intervals (days, weeks, years) between the two dates. For this purpose, MySQL includes the function datediff(), which returns the number of full days between two dates. For example, if I want to know the number of days that my kids will be out of school this summer, I can do the following:

mysql> SELECT DATEDIFF('2019-09-03', '2019-06-21');
+--------------------------------------+
| DATEDIFF('2019-09-03', '2019-06-21') |
+--------------------------------------+
|                                   74 |
+--------------------------------------+
1 row in set (0.00 sec)

Thus, I will have to endure 74 days of poison ivy, mosquito bites, and scraped knees before the kids are safely back at school. The datediff() function ignores the time of day in its arguments. Even if I include a time-of-day, setting it to one second until midnight for the first date and to one second after midnight for the second date, those times will have no effect on the calculation:

mysql> SELECT DATEDIFF('2019-09-03 23:59:59', '2019-06-21 00:00:01');
+--------------------------------------------------------+
| DATEDIFF('2019-09-03 23:59:59', '2019-06-21 00:00:01') |
+--------------------------------------------------------+
|                                                     74 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

If I switch the arguments and have the earlier date first, datediff() will return a negative number, as in:

mysql> SELECT DATEDIFF('2019-06-21', '2019-09-03');
+--------------------------------------+
| DATEDIFF('2019-06-21', '2019-09-03') |
+--------------------------------------+
|                                  -74 |
+--------------------------------------+
1 row in set (0.00 sec)

SELECT DATEDIFF(DAY, '2019-06-21', '2019-09-03')