德瑞克：SQL Server 學習筆記: SQL Server 2012 ：認識 FORMAT 字串函數

適用版本：SQL Server 2012。

FORMAT() 字串函數
傳回以指定格式與選擇性文化特性所格式化的值。

將 FORMAT 函數用於將日期/時間與數值視為字串的地區設定感知格式化作業。
一般類型轉換仍可繼續使用 CAST 或 CONVERT。

語法：

FORMAT ( value, format [, culture ] )

注意事項：

引數 format、culture 是使用 .NET Framework 所支援的功能與文化特性。

請參考以下的範例程式碼：

EX1：認識 FORMAT 字串函數：格式化日期

-- FORMAT 字串函數：格式化日期與時間
DECLARE @td1 DATETIME2 = SYSDATETIME()

SELECT FORMAT( @td1, 'd') N'簡短日期', 
 FORMAT( @td1, 'D') N'完整日期',
 FORMAT( @td1, 'f') N'完整日期和簡短時間',
 FORMAT( @td1, 'F') N'完整日期和完整時間',
 FORMAT( @td1, 't') N'簡短時間',
 FORMAT( @td1, 'T') N'完整時間'
GO

-- 01_FORMAT 字串函數：格式化日期與時間

-- FORMAT 字串函數：使用文化特性與格式化日期
-- 格式模式使用 d：ShortDatePattern
DECLARE @td1 DATETIME ='20010203'

SELECT FORMAT( @td1, 'd', 'en-US') N'en-US_英文 - 美國',
 FORMAT( @td1, 'd', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( @td1, 'd', 'zh-TW') N'zh-TW_中文 - 台灣', 
 FORMAT( @td1, 'd', 'zh-CN') N'zh-CN_中文 - 中國',
 FORMAT( @td1, 'd', 'ko-KR') N'ko-KR_韓文 - 韓國'
GO

-- 02_FORMAT 字串函數：使用文化特性與格式化日期

-- FORMAT 字串函數：使用文化特性與格式化日期
-- 格式模式使用 D：LongDatePattern
DECLARE @td1 DATETIME ='20010203'

SELECT FORMAT( @td1, 'D', 'en-US') N'en-US_英文 - 美國',
 FORMAT( @td1, 'D', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( @td1, 'D', 'zh-TW') N'zh-TW_中文 - 台灣', 
 FORMAT( @td1, 'D', 'zh-CN') N'zh-CN_中文 - 中國',
 FORMAT( @td1, 'D', 'ko-KR') N'ko-KR_韓文 - 韓國'
GO

-- 03_FORMAT 字串函數：使用文化特性與格式化日期

-- FORMAT 字串函數：使用文化特性與格式化日期
-- 格式模式使用 F：FullDateTimePattern (完整日期和完整時間)
DECLARE @td1 DATETIME ='20010203'

SELECT FORMAT( @td1, 'F', 'en-US') N'en-US_英文 - 美國',
 FORMAT( @td1, 'F', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( @td1, 'F', 'zh-TW') N'zh-TW_中文 - 台灣', 
 FORMAT( @td1, 'F', 'zh-CN') N'zh-CN_中文 - 中國',
 FORMAT( @td1, 'F', 'ko-KR') N'ko-KR_韓文 - 韓國'
GO

-- 04_FORMAT 字串函數：使用文化特性與格式化日期

-- FORMAT 字串函數：自訂格式字串格式化日期
DECLARE @td1 DATETIME2 = SYSDATETIME()

SELECT FORMAT( @td1, 'yyyyMMdd') N'8碼日期格式',
 FORMAT( @td1, 'yyyy年MM月dd日 (ddd)') N'完整日期和星期幾縮寫',
 FORMAT( @td1, 'yyyy年MM月dd日 (dddd)') N'完整日期和完整星期幾',
 FORMAT( @td1, 'yyyy年MM月dd日 (ddd)', 'ja-JP') N'完整日期和星期幾縮寫_日文 - 日本',
 FORMAT( @td1, 'yyyy年MM月dd日 (dddd)', 'ja-JP') N'完整日期和完整星期幾_日文 - 日本';

SELECT FORMAT( @td1, 'MMM') N'月份',
 FORMAT( @td1, 'dddd, MMMM, yyyy') N'自訂完整日期',
 FORMAT( @td1, 'hh:mm:ss tt') N'自訂 12 小時制',
 FORMAT( @td1, 'HH:mm:ss tt') N'自訂 24 小時制', 
 FORMAT( @td1, 'HH:mm:ss tt zzz') N'自訂 24 小時制和完整時區位移';
GO

-- 05_FORMAT 字串函數：自訂格式字串格式化日期

-- FORMAT 字串函數：自訂格式化時間
DECLARE @td1 DATETIME2 = SYSDATETIME()

SELECT @td1 N'完整日期與完整時間',
 FORMAT( @td1, '%s') N'秒(不補前置字元0)',
 FORMAT( @td1, 'ss') N'秒(兩位數)',
 FORMAT( @td1, '%m') N'分(不補前置字元0)',
 FORMAT( @td1, 'mm') N'分(兩位數)',
 FORMAT( @td1, '%h') N'12 小時制(不補前置字元0)',
 FORMAT( @td1, 'hh') N'12 小時制(兩位數)',
 FORMAT( @td1, '%H') N'24 小時制(不補前置字元0)',
 FORMAT( @td1, 'HH') N'24 小時制(兩位數)';
GO

-- 06_FORMAT 字串函數：自訂格式化時間

-- FORMAT 字串函數：使用文化特性與格式化日期(星期幾)
DECLARE @td1 DATETIME2 = SYSDATETIME()

SELECT FORMAT( @td1, '%d', 'en-US') N'月份中的日(不補前置字元0)_英文 - 美國',
 FORMAT( @td1, 'dd', 'en-US') N'月份中的日(兩位數)_英文 - 美國',
 FORMAT( @td1, 'ddd', 'en-US') N'星期幾縮寫_英文 - 美國',
 FORMAT( @td1, 'dddd', 'en-US') N'完整星期幾_英文 - 美國';

SELECT FORMAT( @td1, '%d', 'zh-TW') N'月份中的日(不補前置字元0)_中文 - 台灣',
 FORMAT( @td1, 'dd', 'zh-TW') N'月份中的日(兩位數)_中文 - 台灣',
 FORMAT( @td1, 'ddd', 'zh-TW') N'星期幾縮寫_中文 - 台灣',
 FORMAT( @td1, 'dddd', 'zh-TW') N'完整星期幾_中文 - 台灣';

SELECT FORMAT( @td1, '%d', 'ja-JP') N'月份中的日(不補前置字元0)_日文 - 日本',
 FORMAT( @td1, 'dd', 'ja-JP') N'月份中的日(兩位數)_日文 - 日本',
 FORMAT( @td1, 'ddd', 'ja-JP') N'星期幾縮寫_日文 - 日本',
 FORMAT( @td1, 'dddd', 'ja-JP') N'完整星期幾_日文 - 日本';
GO

-- 07_FORMAT 字串函數：使用文化特性與格式化日期(星期幾)

-- FORMAT 字串函數：使用文化特性與格式化日期(月份)
DECLARE @td1 DATETIME2 = SYSDATETIME()

SELECT FORMAT( @td1, '%M', 'en-US') N'數字月份(不補前置字元0)_英文 - 美國',
 FORMAT( @td1, 'MM', 'en-US') N'數字月份(兩位數)_英文 - 美國',
 FORMAT( @td1, 'MMM', 'en-US') N'月份縮寫_英文 - 美國',
 FORMAT( @td1, 'MMMM', 'en-US') N'完整月份_英文 - 美國';

SELECT FORMAT( @td1, '%M', 'zh-TW') N'數字月份(不補前置字元0)_中文 - 台灣',
 FORMAT( @td1, 'MM', 'zh-TW') N'數字月份(兩位數)_中文 - 台灣',
 FORMAT( @td1, 'MMM', 'zh-TW') N'月份縮寫_中文 - 台灣',
 FORMAT( @td1, 'MMMM', 'zh-TW') N'完整月份_中文 - 台灣';

SELECT FORMAT( @td1, '%M', 'ja-JP') N'數字月份(不補前置字元0)_日文 - 日本',
 FORMAT( @td1, 'MM', 'ja-JP') N'數字月份(兩位數)_日文 - 日本',
 FORMAT( @td1, 'MMM', 'ja-JP') N'月份縮寫_日文 - 日本',
 FORMAT( @td1, 'MMMM', 'ja-JP') N'完整月份_日文 - 日本';
GO

-- 08_FORMAT 字串函數：使用文化特性與格式化日期(月份)

-- FORMAT 字串函數：搭配查詢資料表，格式化日期
USE Northwind
GO
SELECT OrderID, OrderDate,
 FORMAT( OrderDate, 'D', 'en-US') N'en-US_英文 - 美國',
 FORMAT( OrderDate, 'D', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( OrderDate, 'D', 'zh-TW') N'zh-TW_中文 - 台灣',
 FORMAT( OrderDate, 'yyyy年MM月dd日 (dddd)', 'zh-TW') N'自訂完整日期與完整星期_zh-TW', 
 FORMAT( OrderDate, 'yyyy年MM月dd日 (dddd)', 'ja-JP') N'自訂完整日期與完整星期_日文 - 日本'
FROM Orders
GO

-- 09_FORMAT 字串函數：搭配查詢資料表，格式化日期

EX2：認識 FORMAT 字串函數：格式化貨幣

-- FORMAT 字串函數：使用文化特性和格式化貨幣
DECLARE @c1 int =12345

SELECT FORMAT( @c1, 'C', 'en-US') N'en-US_英文 - 美國',
 FORMAT( @c1, 'C', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( @c1, 'C', 'zh-TW') N'zh-TW_中文 - 台灣', 
 FORMAT( @c1, 'C', 'zh-CN') N'zh-CN_中文 - 中國',
 FORMAT( @c1, 'C', 'ja-JP') N'ja-JP_日文 - 日本',
 FORMAT( @c1, 'C', 'ko-KR') N'ko-KR_韓文 - 韓國'
GO

-- 10_FORMAT 字串函數：使用文化特性和格式化貨幣

-- FORMAT 字串函數：格式化貨幣，調整輸出小數點位數
-- 示範環境：zh-TW
DECLARE @c1 numeric(6,1) =12345.6

SELECT FORMAT( @c1, 'C0') N'貨幣格式：C0',
 FORMAT( @c1, 'C') N'貨幣格式：C',
 FORMAT( @c1, 'C1') N'貨幣格式：C1', 
 FORMAT( @c1, 'C2') N'貨幣格式：C2', 
 FORMAT( @c1, 'C3') N'貨幣格式：C3',
 FORMAT( @c1, 'C4') N'貨幣格式：C4',
 FORMAT( @c1, 'C5') N'貨幣格式：C5'
GO

-- 11_FORMAT 字串函數：格式化貨幣，調整輸出小數點位數

-- FORMAT 字串函數：搭配查詢資料表，格式化貨幣
USE Northwind
GO
SELECT ProductID, ProductName, UnitPrice,
 FORMAT( UnitPrice, 'C', 'en-US') N'en-US_英文 - 美國',
 FORMAT( UnitPrice, 'C', 'de-DE') N'de-DE_德文 - 德國', 
 FORMAT( UnitPrice, 'C', 'zh-TW') N'zh-TW_中文 - 台灣', 
 FORMAT( UnitPrice, 'C', 'zh-CN') N'zh-CN_中文 - 中國',
 FORMAT( UnitPrice, 'C', 'ja-JP') N'ja-JP_日文 - 日本',
 FORMAT( UnitPrice, 'C', 'ko-KR') N'ko-KR_韓文 - 韓國'
FROM Products
GO

-- 12_FORMAT 字串函數：搭配查詢資料表，格式化貨幣

EX3：認識 FORMAT 字串函數：格式化數字(Number)

-- FORMAT 字串函數：格式化數字(Number)
DECLARE @n1 numeric(7,2) =12345.67

SELECT FORMAT( @n1, 'N') N'格式化數字：N',
 FORMAT( @n1, '#,#.0') N'格式化數字：#,#.0',
 FORMAT( @n1, '#,#.00') N'格式化數字：#,#.00',
 FORMAT( @n1, '#,#.000') N'格式化數字：#,#.000'
GO

-- 13_FORMAT 字串函數：格式化數字(Number)

-- FORMAT 字串函數：搭配查詢資料表，格式化數字
USE Northwind
GO
WITH CTE01
AS
( SELECT OrderID, SUM(UnitPrice * Quantity*(1-Discount)) 'SubTotal'
 FROM [Order Details]
 GROUP BY OrderID )
SELECT OrderID, SubTotal,
 FORMAT( SubTotal, 'N') N'格式化數字：N',
 FORMAT( SubTotal, '#,#.0') N'格式化數字：#,#.0',
 FORMAT( SubTotal, '#,#.00') N'格式化數字：#,#.00',
 FORMAT( SubTotal, '#,#.0000') N'格式化數字：#,#.0000'
FROM CTE01;
GO

-- 14_FORMAT 字串函數：搭配查詢資料表，格式化數字

EX4：認識 FORMAT 字串函數：格式化百分比(Percent)

-- FORMAT 字串函數：格式化百分比(Percent)
-- 百分比：P，結果：乘以 100 並加上百分比符號來顯示的數字。
DECLARE @c1 numeric(6,6) =0.123456

SELECT FORMAT( @c1, 'P0') N'百分比格式：P0',
 FORMAT( @c1, 'P') N'百分比格式：P',
 FORMAT( @c1, 'P1') N'百分比格式：P1', 
 FORMAT( @c1, 'P2') N'百分比格式：P2', 
 FORMAT( @c1, 'P3') N'百分比格式：P3',
 FORMAT( @c1, 'P4') N'百分比格式：P4',
 FORMAT( @c1, 'P5') N'百分比格式：P5'
GO

-- 15_FORMAT 字串函數：格式化百分比(Percent)

EX5. 認識 FORMAT 字串函數：標準數值格式字串

-- FORMAT 字串函數：標準數值格式字串
DECLARE @c1 int = 12

SELECT FORMAT( @c1, 'D') N'十進位：D',
 FORMAT( @c1, 'E') N'指數 (科學記號)：E',
 FORMAT( @c1, 'P') N'百分比：P',
 FORMAT( @c1, 'X') N'十六進位：X',
 FORMAT( @c1, 'X4') N'十六進位：X4',
 FORMAT( @c1, 'X8') N'十六進位：X8'
GO

-- 16_FORMAT 字串函數：標準數值格式字串

認識 FORMAT 字串函數

FORMAT() 字串函數
傳回以指定格式與選擇性文化特性所格式化的值。

語法：

FORMAT ( value, format [, culture ] )

引數說明

(1) value
要格式化之受支援資料類型的運算式。

(2) format
nvarchar 格式模式。

format 引數必須包含有效的 .NET Framework 格式字串，其可以是標準格式字串 (例如 "C" 或 "D")，也可是日期與數值的自訂字元模式 (如 "MMMM dd, yyyy (dddd)")。
不支援複合格式。

如需這些格式模式的完整說明，請參閱 .NET Framework 文件中，有關一般字串格式、自訂日期與時間式，以及自訂數字格式的資訊。您不妨從格式類型主題開始著手。

(3) culture
指定文化特性的選擇性 nvarchar 引數。

如未提供 culture 引數，將會使用目前工作階段的語言。此語言是以 SET LANGUAGE 陳述式隱含或明確加以設定。
culture 的引數可以是 .NET Framework 所支援的任何文化特性，而不限於 SQL Server 明確支援的語言

如果 culture 引數無效，FORMAT 將會產生錯誤。

傳回類型

nvarchar 或 null
傳回值的長度取決於 format。

注意事項

將 FORMAT 函數用於將日期/時間與數值視為字串的地區設定感知格式化作業。
一般類型轉換仍可繼續使用 CAST 或 CONVERT。

發生的錯誤如果不是非 valid 的 culture (如無效的 format)，FORMAT 會傳回 Null。

FORMAT 必須仰賴既存的 .NET Framework Common Language Runtime (CLR)。

因為必須要有 CLR 才可執行此函數，所以無法從遠端進行。
從遠端處理需要 CLR 的函數，會導致遠端伺服器發生錯誤。

值參數的詳細資訊

下表是 value 引數所能接受的資料類型，其中還附有 .NET Framework 對應的資訊：

-- 17_ value 引數所能接受的資料類型