MySQL date_format()函式 - tw511教學網

在本教學中，您將學習如何使用MySQL DATE_FORMAT函式來格式化基於特定格式的日期值。

MySQL DATE_FORMAT函式簡介

要將日期值格式化為特定格式，請使用DATE_FORMAT函式。 DATE_FORMAT函式的語法如下：

DATE_FORMAT(date,format);

DATE_FORMAT()函式接受兩個引數：

date：是要格式化的有效日期值
format：是由預定義的說明符組成的格式字串，每個說明符前面都有一個百分比字元(%)。有關預定義說明符的列表，請參見下表。

DATE_FORMAT函式返回字元集和校對規則取決於用戶端連線的設定的字串。

下表說明了可用於構建日期格式字串的說明符及其含義：

限定符	含義
`%a`	三個字元縮寫的工作日名稱，例如:Mon, Tue, Wed,等
`%b`	三個字元縮寫的月份名稱，例如：Jan，Feb，Mar等
`%c`	以數位表示的月份值，例如：1, 2, 3…12
`%D`	英文字尾如：0th, 1st, 2nd等的一個月之中的第幾天
`%d`	如果是`1`個數位(小於`10`)，那麼一個月之中的第幾天表示為加前導加`0`，如：00, 01,02, …31
`%e`	沒有前導零的月份的日子，例如：1,2，… 31
`%f`	微秒，範圍在`000000..999999`
`%H`	24小時格式的小時，前導加`0`，例如：00,01..23
`%h`	小時，12小時格式，帶前導零，例如：01，02 … 12
`%I`	與`%h`相同
`%i`	分數為零，例如：00,01，… 59
`%j`	一年中的的第幾天，前導為`0`，例如，001,002，… 366
`%k`	24小時格式的小時，無前導零，例如：0,1,2 … 23
`%l`	12小時格式的小時，無前導零，例如：0,1,2 … 12
`%M`	月份全名稱，例如：January, February,…December
`%m`	具有前導零的月份名稱，例如：00,01,02，… 12
`%p`	`AM`或`PM`，取決於其他時間說明符
`%r`	表示時間，`12`小時格式`hh:mm:ss AM`或`PM`
`%S`	表示秒，前導零，如：00,01，… 59
`%s`	與`%S`相同
`%T`	表示時間，24小時格式`hh:mm:ss`
`%U`	週的第一天是星期日，例如：00,01,02 … 53時，前導零的週數
`%u`	週的第一天是星期一，例如：00,01,02 … 53時，前導零的週數
`%V`	與`%U`相同，它與`%X`一起使用
`%v`	與`%u`相同，它與`%x`一起使用
`%W`	工作日的全稱，例如：Sunday, Monday,…, Saturday
`%w`	工作日，以數位來表示(0 = 星期日，1 = 星期一等)
`%X`	周的四位數表示年份,第一天是星期日; 經常與`%V`一起使用
`%x`	周的四位數表示年份,第一天是星期日; 經常與`%v`一起使用
`%Y`	表示年份，四位數，例如2000，2001，…等。
`%y`	表示年份，兩位數，例如00，01，…等。
`%%`	將百分比(`%`)字元新增到輸出

以下是一些常用的日期格式字串：

DATE_FORMAT字串	格式化日期
`%Y-%m-%d`	2017/4/30
`%e/%c/%Y`	4/7/2013
`%c/%e/%Y`	7/4/2013
`%d/%m/%Y`	4/7/2013
`%m/%d/%Y`	7/4/2013
`%e/%c/%Y %H:%i`	4/7/2013 11:20
`%c/%e/%Y %H:%i`	7/4/2013 11:20
`%d/%m/%Y %H:%i`	4/7/2013 11:20
`%m/%d/%Y %H:%i`	7/4/2013 11:20
`%e/%c/%Y %T`	4/7/2013 11:20
`%c/%e/%Y %T`	7/4/2013 11:20
`%d/%m/%Y %T`	4/7/2013 11:20
`%m/%d/%Y %T`	7/4/2013 11:20
`%a %D %b %Y`	Thu 4th Jul 2013
`%a %D %b %Y %H:%i`	Thu 4th Jul 2013 11:20
`%a %D %b %Y %T`	Thu 4th Jul 2013 11:20:05
`%a %b %e %Y`	Thu Jul 4 2013
`%a %b %e %Y %H:%i`	Thu Jul 4 2013 11:20
`%a %b %e %Y %T`	Thu Jul 4 2013 11:20:05
`%W %D %M %Y`	Thursday 4th July 2013
`%W %D %M %Y %H:%i`	Thursday 4th July 2013 11:20
`%W %D %M %Y %T`	Thursday 4th July 2013 11:20:05
`%l:%i %p %b %e, %Y`	7/4/2013 11:20
`%M %e, %Y`	4-Jul-13
`%a, %d %b %Y %T`	Thu, 04 Jul 2013 11:20:05

MySQL DATE_FORMAT範例

我們來看看範例資料庫(yiibaidb)中的orders表，其結果如下所示 -

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set

要選擇訂單的資料並格式化日期值，請使用以下語句：

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;

執行上面查詢語句，得到以下結果 -

+-------------+------------+-------------------+------------------------------+
| orderNumber | orderDate  | requireddate      | shippedDate                  |
+-------------+------------+-------------------+------------------------------+
|       10100 | 2013-01-06 | Sun 13th Jan 2013 | Thursday 10th January 2013   |
|       10101 | 2013-01-09 | Fri 18th Jan 2013 | Friday 11th January 2013     |
|       10102 | 2013-01-10 | Fri 18th Jan 2013 | Monday 14th January 2013     |
|       10103 | 2013-01-29 | Thu 7th Feb 2013  | Saturday 2nd February 2013   
************* 此處省略了一大波資料 *********************************
|       10417 | 2015-05-13 | Tue 19th May 2015 | Tuesday 19th May 2015        |
|       10418 | 2015-05-16 | Sun 24th May 2015 | Wednesday 20th May 2015      |
|       10419 | 2015-05-17 | Thu 28th May 2015 | Tuesday 19th May 2015        
|       10425 | 2015-05-31 | Sun 7th Jun 2015  | NULL                         |
|       10426 | 2017-07-21 | Wed 26th Jul 2017 | Sunday 23rd July 2017        |
+-------------+------------+-------------------+------------------------------+
327 rows in set

要根據格式字串指定的不同日期格式來格式化訂單日期，所需日期和每個訂單的發貨日期。

MySQL DATE_FORMAT與ORDER BY

請參閱以下範例：

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

執行上面查詢語句，得到以下結果 -

+-------------+------------------------------+
| orderNumber | shippeddate                  |
+-------------+------------------------------+
|       10101 | Friday 11th January 2013     |
|       10156 | Friday 11th October 2013     |
|       10356 | Friday 12th December 2014    |
|       10376 | Friday 13th February 2015    |
|       10129 | Friday 14th June 2013        |
|       10279 | Friday 15th August 2014      |
|       10181 | Friday 15th November 2013    |
********* 此處省略了一大波資料 **************** 
|       10348 | Wednesday 5th November 2014  |
|       10411 | Wednesday 6th May 2015       |
|       10267 | Wednesday 9th July 2014      |
|       10300 | Wednesday 9th October 2013   |
+-------------+------------------------------+
313 rows in set

在查詢中，我們選擇了發貨日期不為NULL的所有訂單，並按發貨日期對訂單進行排序。但是，訂單未正確排序。

原因是將shippingdate用作DATE_FORMAT函式的輸出的別名，它是一個字串，ORDER BY子句根據字串值，而不是日期值對別名進行排序。

要解決這個問題，我們必須使用不同於列名的別名; 見下列宣告：

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

執行上面查詢語句，得到以下結果 -

+-------------+------------------------------+
| orderNumber | Shipped date                 |
+-------------+------------------------------+
|       10100 | Thursday 10th January 2013   |
|       10101 | Friday 11th January 2013     |
|       10102 | Monday 14th January 2013     |
|       10104 | Friday 1st February 2013     |
|       10103 | Saturday 2nd February 2013   |
|       10105 | Tuesday 12th February 2013   |
********* 此處省略了一大波資料 **************** 
|       10413 | Saturday 9th May 2015        |
|       10415 | Tuesday 12th May 2015        |
|       10416 | Thursday 14th May 2015       |
|       10417 | Tuesday 19th May 2015        |
|       10419 | Tuesday 19th May 2015        |
|       10418 | Wednesday 20th May 2015      |
|       10426 | Sunday 23rd July 2017        |
+-------------+------------------------------+
313 rows in set

在本教學中，我們向您展示了如何使用MySQL DATE_FORMAT函式根據指定的格式格式化日期。