mysql date 11 59_MySQL DATE_FORMAT函数

Summary: in this tutorial, you will learn how to use theMySQL DATE_FORMATfunction 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 theDATE_FORMATfunction. The syntax of theDATE_FORMATfunction is as follows:DATE_FORMAT(date,format)

The DATE_FORMATfunction 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.

TheDATE_FORMATfunction returns a string withcharacter setandcollationthat 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:SpecifierMeaning%aThree-characters abbreviated weekday name e.g., Mon, Tue, Wed, etc.

%bThree-characters abbreviated month name e.g., Jan, Feb, Mar, etc.

%cMonth in numeric e.g., 1, 2, 3…12

%DDay of the month with English suffix e.g., 0th, 1st, 2nd, etc.

%dDay of the month with leading zero if it is 1 number e.g., 00, 01,02, …31

%eDay of the month without leading zero e.g., 1,2,…31

%fMicroseconds in the range of 000000..999999

%HHour with 24-hour format with leading zero e.g., 00..23

%hHour with 12-hour format with leading zero e.g., 01, 02…12

%ISame as %h

%iMinutes with leading zero e.g., 00, 01,…59

%jDay of year with leading zero e.g., 001,002,…366

%kHour in 24-hour format without leading zero e.g., 0,1,2…23

%lHour in 12-hour format without leading zero e.g., 1,2…12

%MFull month name e.g., January, February,…December

%mMonth name with leading zero e.g., 00,01,02,…12

%pAM or PM, depending on other time specifiers

%rTime in 12-hour format hh:mm:ss AM or PM

%SSeconds with leading zero 00,01,…59

%sSame as %S

%TTime in 24-hour format hh:mm:ss

%UWeek number with leading zero when the first day of week is Sunday e.g., 00,01,02…53

%uWeek number with leading zero when the first day of week is Monday e.g., 00,01,02…53

%VSame as %U; it is used with %X

%vSame as %u; it is used with %x

%WFull name of weekday e.g., Sunday, Monday,…, Saturday

%wWeekday in number (0=Sunday, 1= Monday,etc.)

%XYear for the week in four digits where the first day of the week is Sunday; often used with %V

%xYear for the week, where the first day of the week is Monday, four digits; used with %v

%YFour digits year e.g., 2000, 2001,…etc.

%yTwo digits year e.g., 10,11,12, etc.

%%Add percentage (%) character to the output

The following are some commonly used date format strings:DATE_FORMAT stringFormatted date%Y-%m-%d7/4/2013

%e/%c/%Y4/7/2013

%c/%e/%Y7/4/2013

%d/%m/%Y4/7/2013

%m/%d/%Y7/4/2013

%e/%c/%Y %H:%i4/7/2013 11:20

%c/%e/%Y %H:%i7/4/2013 11:20

%d/%m/%Y %H:%i4/7/2013 11:20

%m/%d/%Y %H:%i7/4/2013 11:20

%e/%c/%Y %T4/7/2013 11:20

%c/%e/%Y %T7/4/2013 11:20

%d/%m/%Y %T4/7/2013 11:20

%m/%d/%Y %T7/4/2013 11:20

%a %D %b %YThu 4th Jul 2013

%a %D %b %Y %H:%iThu 4th Jul 2013 11:20

%a %D %b %Y %TThu 4th Jul 2013 11:20:05

%a %b %e %YThu Jul 4 2013

%a %b %e %Y %H:%iThu Jul 4 2013 11:20

%a %b %e %Y %TThu Jul 4 2013 11:20:05

%W %D %M %YThursday 4th July 2013

%W %D %M %Y %H:%iThursday 4th July 2013 11:20

%W %D %M %Y %TThursday 4th July 2013 11:20:05

%l:%i %p %b %e, %Y7/4/2013 11:20

%M %e, %Y4-Jul-13

%a, %d %b %Y %TThu, 04 Jul 2013 11:20:05

MySQL DATE_FORMAT examples

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

To select order’s data and format the date value, you use the following statement:SELECTorderNumber,DATE_FORMAT(orderdate,'%Y-%m-%d')orderDate,DATE_FORMAT(requireddate,'%a%D%b%Y')requireddate,DATE_FORMAT(shippedDate,'%W%D%M%Y')shippedDateFROMorders;

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:SELECTorderNumber,DATE_FORMAT(shippeddate,'%W%D%M%Y')shippeddateFROMordersWHEREshippeddateISNOTNULLORDERBYshippeddate;

In the query, we selected all orders whose shipped date are notNULLand sorted the orders by the shipped date. However, the orders were not sorted correctly. The reason is we usedshippeddateas thealiasfor the output of theDATE_FORMATfunction, 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:SELECTorderNumber,DATE_FORMAT(shippeddate,'%W%D%M%Y')'Shippeddate'FROMordersWHEREshippeddateISNOTNULLORDERBYshippeddate;

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

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