MySQL Tutorial

Using Expressions in MySQL Statements

MySQL Expressions

The SQL statements can use either column names or expressions. An expression is a type of formula that helps define a value that is derived from other data types via some modifications, such as adding of two numbers. An expression can include column names, literal values, operators, and functions.

An operator is a symbol that represents the action that should be taken over a set of values. One example is the greater than (>) comparison operator used to compare values to determine if one value is greater than the other. A function is a code artifact that performs a predefined task such as calculating a logrithm of a number.

Both operators and functions are often an integral part of an expression.

Basic Elements of MySQL Expressions

Expressions in MySQL are made of some basic elements, which can be combined to create fairly involved and complex filters. Expressions control actions taken by SQL statements in WHERE clause of DML statements.

• Literals: Constant values such as strings, integers, dates used as-is in an expression.
• Column names: Replaced with the value from column for a row as contained in database.
• Operators: An operator is used in conjunction with various values and is replaced with a derived result value.
• Functions: Perform specific task on arguments passed and is replaced with its return value similar to an operator. These arguments can themselves be other expressions.

For each row that the SELECT statement returns, the expression is calculated and the result inserted in the appropriate columns. When you use arithmetic operators in an expression, the components of that expression are evaluated according to the basic formulaic principles of mathematics.

Operators in MySQL

In this lesson of the MySQL tutorial, we learn the use of operators in following categories:

• Arithmetic operators to perform calculations.
• Comparison operators to compare arguments within an expression.
• Logical operators to join multiple expressions.
• Bitwise operators to do bit-by-bit comparison of numerical values.
• Sort operators to manage collation and case-sensitivity for searching and sorting operations.

Operators are components of expressions to specify the interactions and conditions that affect and limit the range of values in a result set.

Expressions can be simple with just one argument with zero or one operator, or made of several different elements. The term argument describes the operand or non-operator part of an expression, such as the column name or the literal value.

Calculated Fields

Calculated fields are fields that do not exist in a table, but are created in the SELECT statement. For example, you might want to create a FullName from FirstName and LastName.

Concatenation

Concatenation is a basic function for stringing together different words or characters.

-- Select the full name of all employees. MySQL.
SELECT CONCAT(first_name, ' ', last_name)
FROM staff;
SELECT CONCAT(title, ', Rented @ ', rental_rate) from film where film_id = 101;

Note: As concatenation works with strings, MySQL will convert any other data types to string first, as shown for rental rate example.

Arithmetic Operators

Mathematical calculations in SQL are performed using Arithmetic Operators, similar to those in other languages.

SELECT rental_rate / 2 FROM film_detail WHERE category_name = 'Comedy';
SELECT 1+(2*3);
SELECT 16 % 3;

Some simple Math expressions.

Note: For many of the examples using functions and calculated columns, we will provide Aliases to describe the column.

Operator Precedence

The expression evaluation follows an order in which operators are used. The operators are prioritized as listed from the highest precedence to the lowest:

1. BINARY, COLLATE
2. NOT (logical negation), ! (logical negation)
3. - (unary minus), ~ (unary bit inversion)
4. ^ (bitwise exclusive OR comparison)
5. * (multiplication), / (division), % (modulo)
6. - (subtraction), + (addition)
7. << (bitwise shift left), >> (bitwise shift right)
8. & (bitwise AND)
9. | (bitwise OR)
10. Comparison operators such as < >
11. BETWEEN, NOT BETWEEN
12. AND && (conjuction - logical addition)
13. XOR (logical exclusive OR comparison)
14. OR || (disjunction - either/or comparison)

As per list, the comparison BINARY operator has precedence over the BETWEEN and ampersand (&) operators. Operators on the same line above have the same level of precedence, and are evaluated in the order of usage.

For safety, use parenthesis for explicit grouping of operators to avoid unintended effects:
SELECT 8 + 5 * 2;
The expression above gives wrong results (18) and is better replaced by:
SELECT ( 8 + 5 ) * 2;

Comparison Operators

Comparison operators match the arguments on either side of the expression and determine whether the condition is true, false, or NULL. If either argument is NULL, the condition is considered NULL.

Tip: The exception here is NULL-safe (<=>) operator, which evaluates to true when both arguments are the same, even if they are both NULL.

First, let us see some boolean expressions as result sets in Select.

SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
SELECT 12345 IS NULL, 34456 IS NULL, NULL IS NULL;
SELECT 'data' IS NOT NULL, 'data2' IS NOT NULL, NULL IS NOT NULL;

Show various boolean conditions.

SELECT * FROM film WHERE rental_rate >= 2.99;
SELECT * FROM customer WHERE last_name >= "WI";

Show comparison operators.

Next, we want to see list of movies only in Children category where name of Studio is specified, or not null. The NOT keyword will return all Non-Null studios.

SELECT title, category_name
FROM film_detail
WHERE studio_id IS NOT NULL
AND category_name = 'Children'
ORDER BY title;

In many cases, we would selectively go after rows where data is indeed present, as absence of data may indicate a lack of trust or inconsistency.

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

SELECT title, category_name, in_stock, avail_count
FROM film_detail
WHERE in_stock BETWEEN 3 AND 5
AND category_name = 'Children'
ORDER BY title;

Select movies in a category where stock is in a given range.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Here, we list movies belonging to a list of categories, instead of a single category.

SELECT title, category_name
FROM film_detail
WHERE category_name IN ('Children','Family')
ORDER BY title;

Select movies in one of given categories.

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern, and searches for less-than-exact but similar values. The LIKE operator supports the use of two wildcards:

• Percentage (%): Represents zero or more values.
• Underscore (_): Represents exactly one value.

The following SELECT statement includes a WHERE clause expression that searches for film_title that start with "FARGO", by using the percentage wildcard after the lookup value.

SELECT title, category_name
FROM film_detail
WHERE title LIKE 'FARGO%'
ORDER BY category_name, title;

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly for searching against large tables.

The REGEXP Operator

Regular Expressions are commonly used for creating complex searches. Here is an example of using a REGEXP (Regular Expression) match:

SELECT title, category_name
FROM film_detail
WHERE title REGEXP '^AIRP[LO]'
ORDER BY title;

-- Some other REGEXP examples:
SELECT 'abcabc' REGEXP 'abc',    'abcabc' REGEXP 'cb';

-- The search pattern may describe only a part of string. To match entire string, use ^ and $ in the search:
SELECT 'abc' REGEXP '^abc$', 'abcabc' REGEXP '^abc$';

-- 
SELECT 'cde' REGEXP '[a-c]+', 'efg' REGEXP '[a-c]+';


SELECT 'abcabc' REGEXP 'ABC', 'abcabc' REGEXP BINARY 'ABC';

The expression file_title REGEXP '^AIRP[LO]' - contains the caret (^) symbol to specify that column value must begin with word AIRP followed by a L or an O.

Use of BINARY with Strings

CREATE TABLE color (name VARCHAR(20));
INSERT INTO color VALUES('Red'),('red'),('blue'),('green'),('RED'),('REd'),('reD'),('Black'),('BLACK'),('Green'),('Yellow');

SELECT * FROM color WHERE name = 'red';
SELECT * FROM color WHERE name = BINARY 'red';

SELECT * FROM color WHERE name LIKE 'gr%';
SELECT * FROM color WHERE name LIKE BINARY 'green';

SELECT * FROM color WHERE name REGEXP 'b[a-z]+k';
SELECT * FROM color WHERE name REGEXP BINARY 'b[a-z]+k';

A table is created and populated with some color names.

Several searches are shown, which result in finding data without case senstivity.

BINARY causes exact string to be used.

The NOT Operator

The NOT operator is used to negate an operation.

SELECT title, category_name
FROM film_detail
WHERE NOT category_name IN ('Horror','Drama')
ORDER BY title;

SELECT title, category_name, rental_rate
FROM film_detail
WHERE rental_rate NOT BETWEEN 4 and 5
AND title NOT REGEXP '^[A-T]'
ORDER BY title;

Some examples of using NOT with other operators.

Using CASE

CASE functions contain one or more WHEN clauses as shown below.

--OPTION 1
SELECT CASE column
WHEN VALUE1 THEN RETURN_VALUE1
WHEN VALUE2 THEN RETURN_VALUE2
ELSE RETURN_VALUE_DEFAULT
END
AS ColumnName
FROM table

--OPTION 2
SELECT CASE
WHEN EXPRESSION1 THEN RETURN_VALUE1
WHEN EXPRESSION2 THEN RETURN_VALUE2
ELSE RETURN_VALUE_DEFAULT
END
AS ColumnName
FROM table

SELECT title, rental_rate, CASE WHEN rental_rate > 4.0 THEN 'too high'
   WHEN rental_rate > 3.0 THEN 'high'
   ELSE 'bearable'
END
FROM film_detail
WHERE rating = 'PG'
AND category_name = 'Children';

The various cases contain different matching expressions:

SELECT film_id, CASE in_stock
   WHEN 8 THEN 'great'
   WHEN 7 THEN 'great'
   WHEN 6 THEN 'good'
   WHEN 5 THEN 'good'
   WHEN 4 THEN 'good'
   WHEN 3 THEN 'fine'
   WHEN 2 THEN 'ok'
   ELSE 'low'
END
FROM film_detail
LIMIT 5;

The various cases contain exact match values:

Logical Operators

Logical operators allow you to test the validity of multiple expressions. Through the use of these operators, you can associate expressions to determine whether the conditions, when taken as a whole, evaluate to true, false, or NULL. For a condition or set of conditions to be acceptable, they must evaluate to true. The following table describes the logical operators available in MySQL.

SELECT title, category_name, in_stock
FROM film_detail
WHERE category_name = 'Children' AND in_stock > 2
ORDER BY title;

The two expressions here are connected by the AND logical operator, and both expressions must evaluate to true in order for the whole condition to evaluate to true.

SELECT title, category_name, in_stock
FROM film_detail
WHERE category_name = 'Children' AND in_stock > 2
ORDER BY title;

SELECT title, category_name, in_stock
FROM film_detail
WHERE category_name = 'Horror' OR category_name = 'Drama' 
ORDER BY title;

Here, we use an OR operator to connect two conditions, and either of the expressions must evaluate to true for the whole condition ot be true.

SELECT title, category, in_stock
FROM film_detail
WHERE category_name = 'Children' XOR in_stock < 2
ORDER BY title
LIMIT 10;

With an XOR operator to connect conditions, the whole expression evaluates to true if exactly one of the conditions evaluates to true, but not both.

We want to order more films in either 'Children' category or where stock is less than 2.

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

SELECT title, category_name, rating, rental_rate, length
FROM film_detail
WHERE category_name = 'Children' OR category_name = 'Drama' OR category_name = 'Family'
 AND rating = 'PG';

We see several films which are not rated 'PG', not what we intended.

This can be fixed by putting the OR portion of the clause in parentheses.

SELECT title, category_name, rating, rental_rate, length
FROM film_detail
WHERE (category_name = 'Children' OR category_name = 'Drama' OR category_name = 'Family')
 AND rating = 'PG';

The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the films in given categories, that are rated 'PG'

If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.

Summary

Using MySQL Functions

In addition to operators, functions aid creating expressions for further manipulating column and literal values.

In this lesson of the MySQL tutorial, you will learn about many of the functions included in MySQL, their purpose and results:

• Comparison functions: Compare values in an expression
• Cast functions to convert data to other types.
• Data-specific functions: Special operations on string, numerical, and date/time data.
• Aggregate functions: Group data in a SELECT statement for summaries.
• Control Flow functions: Results based on specifications in the functions.
• System-related functions: Encrypt and decrypt data, information on system, queries and operations.

Functions to Compare Data

Use GREATEST() / LEAST() to Compare two or more values and return the value that is highest/lowest. The values specified can be numeric, string, or date/time values and are compared based on the current character set.

SELECT GREATEST(4, 83, 0, 9, -3);
SELECT LEAST('e','a','d','c');

COALESCE() returns the first non-NULL argument in the list, else NULL if all arguments are NULL.

SELECT COALESCE(column1, column2, default-value);

In this case, if column1 is NULL, column2 is returned, else column1 is returned. If both column values are null, default-value is returned.

SELECT inventory_id, customer_id, rental_date, 
COALESCE( CAST(return_date AS CHAR), '**Still Out**' ) AS 'Returned?'
FROM rental
WHERE customer_id = 590
AND rental_date > '20050731';

Check if to see if a Film is still out via an Coalesce() function on return_date column:

Control Flow Functions

The types of functions that you look at next are those that return a result by comparing conditions. The returned value is determined by which condition is true.

If first <expression1> in a IF() evaluates to true, then the function returns <expression2>; else the function returns <expression3>.

SELECT film_id, title, rating, IF ( rating IN ('G','PG','PG-13'), 'OK','Not OK') AS 'Children?'
FROM film
WHERE title BETWEEN 'SR' AND 'T';

Check if to see if a Film is ok for children age 13 using an IF() function on rating column:

In IFNULL(), first <expression> is returned if it is not NULL; else second <expression> is returned.

SELECT inventory_id, customer_id, rental_date, IFNULL ( CAST(return_date AS CHAR), '**Still Out**' ) AS 'Returned?'
FROM rental;

Check if to see if a Film is still out via an IFNULL() function on return_date column:

Exercise :Using Control Flow Functions in a SELECT Statement

Selectively do this .....:

ISNULL() checks if the contained expression NULL.

SELECT ISNULL(column1);

Data Conversion Functions

CONVERT() and CAST functions allow you to convert values to a specific type of data or to assign a character set to a value.

CAST(<expression> AS <type>)

The function converts the value returned by the expression to the specified conversion type, which follows the AS keyword. The CAST() function supports a limited number of conversion types, the CONVERT() function) and serve a slightly different purpose, which is to specify how the data is converted. Data types, on the other hand, specify the type of data that can be inserted in a column.

The conversion types available to the CAST() function are as follows:

• BINARY

• CHAR

• DATE

• DATETIME

• SIGNED [INTEGER]

• TIME

• UNSIGNED [INTEGER]

For example, you might have a numeric value (either a literal value or one returned by an expression) that you want converted to the DATE conversion type.

SELECT film_id, CONCAT_WS( '-', 'Rental Rate', CAST(rental_rate AS CHAR) ) FROM film WHERE film_id BETWEEN 991 AND 999;
SELECT customer_id, inventory_id, CAST(rental_date AS UNSIGNED INTEGER) FROM rental WHERE customer_id = 103;

SELECT CAST(20080325 AS DATE);
SELECT '1234' + '3456';
SELECT CAST('1234' AS INT) + CAST('3456' AS INT);

The various selects use CAST() to convert data values from one form to another:

String Functions

As you would guess, string functions allow you to manipulate and extract string values. MySQL supports numerous string functions. This section covers those that you're most likely to use in your applications and provides examples of each of them.

SELECT UPPER('Cats and Dogs');
SELECT LOWER('Cats and Dogs');
SELECT SUBSTRING('cats and dogs and more dogs', 10, 4);

Basic string functions:

Use STRCMP() to Compare two string expressions using current character set, return a 0 if strings are equal, a -1 if first expression is lesser than second. If first expression is higher or the comparison causes a NULL return, this function returns a 1.

SELECT STRCMP('begin', 'bigger');

'begin' is smaller than 'bigger' alphabetically, the statement returns a -1.

The ASCII() function allows you to identify the numeric value of the first character in a string.

SELECT ASCII('book');
Returns 98, or ASCII char value of 'b'

CHAR_LENGTH() and CHARACTER_LENGTH() are synonymous, and return the number of characters in the string.

SELECT CHAR_LENGTH('happiness');
Return 9 (number of characters in the string)

The LENGTH() function also returns the length of a string in bytes rather than characters.

SELECT LENGTH('A very short string');

CONCAT() concatenate two must specify two or more string values separated by commas.

SELECT CONCAT('Akita', ' ', 'Akbash', ' ', 'dogs');

Notice that the second and fourth values are spaces. This ensures that a space is provided between each of the three words. Another way to use the spaces formally as a separator is the CONCAT_WS() function.

CONCAT_WS(<separator>, <string1>, <string2> [{, <string>}...])

By using CONCAT_WS() function, the separator is automatically inserted between the values. If one of the values is NULL, the separator is not used. Except for the separator, the CONCAT_WS() function is the same as the CONCAT() function. For example, the following SELECT statement concatenates the same words as in the last example:

SELECT CONCAT_WS(' ', 'Australian', 'Shepherd', 'dogs');

INSTR() function returns the position number where the substring is located in the main string.

SELECT INSTR('Breeder dogs', 'dogs') 'Where Are Dogs?';
Returns a value of 9

LOCATE() and INSTR() functions are similar except with LOCATE(), the substring is listed first:

SELECT LOCATE('dogs', 'Breeder dogs');

LOCATE() function may also include a third argument, <position>, which identifies a starting position to start looking for the substring.

SELECT LOCATE('dogs', 'Friendly dogs and Lovely dogs', 15);
Returns 26, the "second" instance of dogs string.

LEFT() and RIGHT() Functions

MySQL also provides functions that return only a part of a string value. For example, you can use the LEFT() function to return only a specific number of characters from a value, as shown in the following syntax:

SELECT LEFT('Golden Retriever', 6);

We want 6 characters from left as specified in the function arguments, the function returns the value Golden.

SELECT RIGHT('Golden Retriever', 9);

The statement returns the value Retriever, 9 characters from right.

REPEAT() and REVERSE() Functions

The REPEAT() function replicates a given string a specific number of times. The values are then concatenated and returned.

SELECT REPEAT('**', 3);

The result from this function is ****** .

We can reverse the order of characters in a string with REVERSE() function:

SELECT REVERSE('!is rotator');
Returns rotator si!

SUBSTRING() Function

SUBSTRING() function, which includes several forms, returns a substring from the identified string. The first form of the SUBSTRING() function, you must specify the string and the starting position. The function then returns a substring that includes the rest of the string value, starting at the identified position. You can achieve the same results by using the following syntax:

SUBSTRING(<string> FROM <position>)

To separate arguments, one can use the FROM keyword or the a comma. For example, the following call returns dogs, starting from ninth position.

SELECT SUBSTRING('Breeder dogs', 9);

The SUBSTRING() function here provides only a starting position but no ending position.

MySQL supports another form of the SUBSTRING() function:

SUBSTRING(<string>, <position>, <length>)

This form includes the <length> argument, which allows you to specify how long (in characters) the substring should be. You can also use the following format to specify the length:

SUBSTRING(<string> FROM <position> FOR <length>)

In this case, we use the FROM and FOR keywords instead of commas to separate the arguments. The following example demonstrates how to specify a length:

SELECT SUBSTRING('Breeder dogs and Fancy dogs', 9, 4)  AS 'My Friends';

In the function call after the main string, the arguments identify the starting position 9 and the length of the substring 4. The function returns dogs.

SELECT SUBSTRING('Breeder dogs and Fancy dogs' FROM 9 FOR 4) AS 'Best Friends';

String functions allow you to manipulate and extract string values. In the following demos, we use several of these String functions with our SELECT statements.

SELECT REVERSE('!is rotator');
SELECT REPEAT('**', 3);
SELECT LEFT('Golden Retriever', 6);
SELECT RIGHT('Golden Retriever', 9);

SELECT LENGTH('A very short string');
SELECT CHAR_LENGTH('happiness');

SELECT LOCATE('dogs', 'Breeder dogs');
SELECT LOCATE('dogs', 'Friendly dogs and Lovely dogs', 15);
SELECT INSTR('Breeder dogs', 'dogs') 'Where Are Dogs?';
SELECT CONCAT('Akita', ' ', 'Akbash', ' ', 'dogs');
SELECT CONCAT_WS(' ', 'Australian', 'Shepherd', 'dogs');

SELECT STRCMP('begin', 'bigger');

Additional string functions

Numeric Functions

Now we look at numeric functions. Numeric functions allow you to perform calculations on numeric values. MySQL supports various numeric functions that allow you to perform advanced mathematical operations. This section covers many of the more common numeric functions.

The CEIL() and CEILING() functions, which are synonymous, return the smallest integer that is not less than the specified number:

SELECT CEILING(9.327);
-- Returns 10

The value 10 is returned as it is the smallest integer that is not less than 9.327. To return largest integer that is not greater than a specified value, use the FLOOR() function.

SELECT FLOOR(9.327);
-- Returns 9

The MOD() function is similar to the modulus (%) arithmetic operator.

SELECT MOD(22, 7);
-- Returns 1, the remainder of the division.

The POW() and POWER() functions raise the value of one number to the power of the second number, as shown in the following syntax:

SELECT POW(4, 2);
-- Returns 16.

Using ROUND(), We can round off a fractional number, optionally to a given number of decimal places.

SELECT ROUND(4.27943, 2);
-- Returns 4.28 (rounded to two decimal places).

The TRUNCATE() function simply drops the fractional part.

SELECT TRUNCATE(4.27943, 2);
-- This time, the value 4.27 is returned

The SQRT() function returns to the square root of a specified number:

SELECT SQRT(36);
-- Returns a value of 6.

SELECT CEIL(9.327);
SELECT FLOOR(9.327);
SELECT POW(4, 2);
SELECT MOD(22, 7);
SELECT ROUND(4.27943, 2);
SELECT TRUNCATE(4.27943, 2);
SELECT SQRT(36);

SELECT title, ROUND(rental_rate,1)  AS ApproxRate
FROM film;

Some usage of Math functions

Date/Time Functions

The next set of functions covered are those related to date and time values. These functions are handy for comparing and calculating dates and times as well as returning the current dates and times. MySQL supports numerous date/time functions, and this section covers many of those.

SELECT FROM_DAYS(729669);
SELECT DATEDIFF('2008-06-30','2008-06-01');

Some common date functions in MySQL.

Current Date/Time

MySQL includes a number of functions that allow you to retrieve current date and time information.

SELECT NOW(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

SELECT DAYNAME(NOW());

This script is a collection of several date-based functions for current date/time:

Date Addition/Subtraction Functions

The ADDDATE() and DATE_ADD() functions are synonymous, and add date-related intervals to date values.

ADDDATE(<date>, INTERVAL <expression> <type>)

The function includes two arguments, the <date> value and the INTERVAL clause. The <date> value can be any date or date/time literal value or value derived from an expression. This value acts as the root value to which time is added. The INTERVAL clause requires an <expression>, which must be a time value in an acceptable format, and a <type> value. The following table lists the types that you can specify in the INTERVAL clause and the format for the expression used with that type:

The following SELECT statement use the ADDDATE() and SUBDATE() functions to add or subtract values against a given date/time value:

SELECT ADDDATE('2007-10-31 13:39:59', INTERVAL '10:20' HOUR_MINUTE);
SELECT SUBDATE('2007-10-31 23:59:59', INTERVAL '12:10' HOUR_MINUTE);
SELECT SUBDATE('2007-10-31 23:59:59', INTERVAL '12:10' YEAR_MONTH);

This script is a collection of several examples of adding to and subtracting elements from a date and output is shown below:

The first argument to ADDDATE() and SUBDATE() function is the base date/time value, and the second argument is the INTERVAL clause to add or subtract.

Date Extraction Methods

Another useful time-related function is the EXTRACT() function, which can return INT Value of several date components for a given date.

SELECT EXTRACT(YEAR_MONTH FROM '2004-12-31 23:59:59');
SELECT EXTRACT(HOUR_MINUTE FROM '2004-12-31 23:59:59');
SELECT EXTRACT(MONTH FROM '2004-12-31 23:59:59');
SELECT EXTRACT(DAY FROM '2004-12-31 23:59:59');

SELECT YEAR(rental_date) Year, MONTH(rental_date) Month, 
DAY(rental_date) Day, 
DAYOFWEEK(rental_date) DayOfWeek, DAYOFYEAR(rental_date) DayOfYear,
MONTH(return_date) MonthReturn
FROM rental 
WHERE customer_id = 101
AND MONTH(return_date) BETWEEN 3 AND 9;

This script is a collection of several examples of extract elements from a date and output is shown below:

Date Formatting Methods

It is required many times to format a date value according to a certain. The DATE_FORMAT(date,format) function uses a format string to output date in desired style.

The following specifiers may be used in the format string. The % character is required before format specifier characters .

There is some support for zero days and months in the formats above.

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
SELECT DATE_FORMAT('2006-06-00', '%d');
SELECT DATE_FORMAT('1999-01-01', '%X %V');
SELECT DATE_FORMAT('2005-12-31', '%M %d %Y');
--   December 31 2005
SELECT DATE_FORMAT('2005-12-31', '%D of %M');
--   31st of December
SELECT TIME_FORMAT('02:17', '%H');
--   02

Several date formatting statements are shown.

All material in this Using Expressions in MySQL Statements is copyright 2010 Webucator. The purpose of this website is to help you learn MySQL on your own and use of the website implies your agreement to our MySQL Tutorial Terms of Service.