MySQL DATE_FORMAT函数

Summary: in this tutorial, you will learn how to use the MySQL DATE_FORMAT function to format a date value based on a specific format.

Introduction to MySQL DATE_FORMAT function

To format a data value to a specific format, you use the DATE_FORMAT function. The syntax of the DATE_FORMATfunction is as follows:

DATE_FORMAT(date,format)

The  DATE_FORMAT function accepts two arguments:

• date: is a valid date value that you want to format

• format: is a format string that consists of predefined specifiers. Each specifier is preceded by a percentage character ( %). See the table below for a list of predefined specifiers.

The DATE_FORMAT function returns a string with character set and collation that depend on the settings of the client’s connection.

The following table illustrates the specifiers and their meanings that you can use to construct date format string:

The following are some commonly used date format strings:

MySQL DATE_FORMAT examples

Let’s take a look at the orders table in the sample database.

To select order’s data and format the date value, you use the following statement:

SELECT orderNumber,        DATE_FORMAT(orderdate,'%Y-%m-%d')  orderDate,        DATE_FORMAT(requireddate,'%a %D %b %Y')  requireddate,        DATE_FORMAT(shippedDate,'%W %D %M %Y')  shippedDate FROM orders;

We formatted the order date, required date and shipped date of each order based on different date formats specified by the format strings.

MySQL DATE_FORMAT with ORDER BY

See the following example:

SELECT orderNumber,         DATE_FORMAT(shippeddate,'%W %D %M %Y') shippeddate FROM orders WHERE shippeddate IS NOT NULL ORDER BY shippeddate;

In the query, we selected all orders whose shipped date are not NULL and sorted the orders by the shipped date. However, the orders were not sorted correctly. The reason is we used shippeddate as the alias for the output of the DATE_FORMAT function, which is a string, the ORDER BY clause chose the alias and sorted the orders based on string, not date.

To fix this problem, we have to use an alias that is different from the column name; see the following statement:

SELECT orderNumber,     DATE_FORMAT(shippeddate,'%W %D %M %Y') 'Shipped date' FROM orders WHERE shippeddate IS NOT NULL ORDER BY shippeddate;

In this tutorial, we have shown you how to use the MySQL DATE_FORMAT function to format date based on a specified format.

原文链接：http://outofmemory.cn/mysql/function/mysql-date_format