FORMAT (Transact-SQL)FORMAT (Transact-SQL)

本主題適用於:是SQL Server (從 2012 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

傳回以 SQL Server 2017SQL Server 2017 中指定的格式與選用文化特性格式化的值。Returns a value formatted with the specified format and optional culture in SQL Server 2017SQL Server 2017. 將 FORMAT 函數用於將日期/時間與數值視為字串的地區設定感知格式化作業。Use the FORMAT function for locale-aware formatting of date/time and number values as strings. 針對一般資料類型轉換,請使用 CAST 或 CONVERT。For general data type conversions, use CAST or CONVERT.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

FORMAT ( value, format [, culture ] )  

引數Arguments

valuevalue
要格式化之受支援資料類型的運算式。Expression of a supported data type to format. 如需有效類型的清單,請參閱下面<備註>一節中的表格。For a list of valid types, see the table in the following Remarks section.

formatformat
nvarchar 格式模式。nvarchar format pattern.

format 引數必須包含有效的 .NET Framework 格式字串,其可以是標準格式字串 (例如 "C" 或 "D"),也可以是日期與數值的自訂字元模式 (如 "MMMM DD, yyyy (dddd)")。The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)"). 不支援複合格式。Composite formatting is not supported. 如需這些格式模式的完整說明,請參閱 .NET Framework 文件中有關一般字串格式、自訂日期與時間格式,以及自訂數字格式的資訊。For a full explanation of these formatting patterns, consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. 您不妨從「格式類型」主題開始著手。A good starting point is the topic, "Formatting Types."

cultureculture
指定文化特性的選用 nvarchar 引數。Optional nvarchar argument specifying a culture.

如未提供 culture 引數,將會使用目前工作階段的語言。If the culture argument is not provided, the language of the current session is used. 此語言是以 SET LANGUAGE 陳述式隱含或明確加以設定。This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture 的引數可以是 .NET Framework 所支援的任何文化特性,而不限於 [SQL Server]SQL Server 明確支援的語言。culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by [SQL Server]SQL Server. 如果 culture 引數無效,FORMAT 會引發錯誤。If the culture argument is not valid, FORMAT raises an error.

傳回類型Return Types

nvarchar 或 nullnvarchar or null

傳回值的長度取決於 formatThe length of the return value is determined by the format.

RemarksRemarks

FORMAT 會針對不是 validculture 以外的錯誤傳回 NULL。FORMAT returns NULL for errors other than a culture that is not valid. 例如,如果 format 中指定的值無效,則會傳回 NULL。For example, NULL is returned if the value specified in format is not valid.

FORMAT 函數不具決定性。The FORMAT function is nondeterministic.

FORMAT 必須仰賴既存的 .NET Framework Common Language Runtime (CLR)。FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).

因為必須要有 CLR 才可以執行此函數,所以無法從遠端進行。This function cannot be remoted since it depends on the presence of the CLR. 從遠端處理需要 CLR 的函數可能會導致遠端伺服器發生錯誤。Remoting a function that requires the CLR, could cause an error on the remote server.

FORMAT 會依賴 CLR 格式規則,這些規則規定必須逸出冒號和句號。FORMAT relies upon CLR formatting rules, which dictate that colons and periods must be escaped. 因此,當格式字串 (第二個參數) 包含冒號或句號時,如果輸入值 (第一個參數) 屬於 time 資料類型,則必須以反斜線逸出冒號或句號。Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type. 請參閱 D. 具有 time 資料類型的 FORMATSee D. FORMAT with time data types.

下表列出 value 引數可接受的資料類型,以及其 .NET Framework 對應的對等類型。The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

類別目錄Category 類型Type .NET 類型.NET type
數值Numeric bigintbigint Int64Int64
數值Numeric ssNoversionint Int32Int32
數值Numeric SMALLINTsmallint Int16Int16
數值Numeric TINYINTtinyint ByteByte
數值Numeric decimaldecimal SqlDecimalSqlDecimal
數值Numeric NUMERICnumeric SqlDecimalSqlDecimal
數值Numeric FLOATfloat DoubleDouble
數值Numeric REALreal SingleSingle
數值Numeric SMALLMONEYsmallmoney DecimalDecimal
數值Numeric moneymoney DecimalDecimal
日期及時間Date and Time 日期date DateTimeDateTime
日期及時間Date and Time timetime TimeSpanTimeSpan
日期及時間Date and Time DATETIMEdatetime DateTimeDateTime
日期及時間Date and Time smalldatetimesmalldatetime DateTimeDateTime
日期及時間Date and Time datetime2datetime2 DateTimeDateTime
日期及時間Date and Time datetimeoffsetdatetimeoffset DateTimeOffsetDateTimeOffset

範例Examples

A.A. 簡單的 FORMAT 範例Simple FORMAT example

下列範例會傳回針對不同文化特性格式化的簡單日期。The following example returns a simple date formatted for different cultures.

DECLARE @d DATETIME = '10/01/2011';  
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'  
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'  
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'  
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result';   

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'  
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'  
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'  
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';  

以下為結果集:Here is the result set.

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result  
----------------  ----------------------------- ------------- -------------------------------------  
10/1/2011         01/10/2011                    01.10.2011    2011/10/1  

(1 row(s) affected)  

US English Result            Great Britain English Result  German Result                    Chinese (Simplified PRC) Result  
---------------------------- ----------------------------- -----------------------------  ---------------------------------------  
Saturday, October 01, 2011   01 October 2011               Samstag, 1. Oktober 2011        2011年10月1日  

(1 row(s) affected)  

B.B. 包含自訂格式字串的 FORMATFORMAT with custom formatting strings

下列範例會藉由指定自訂格式顯示格式數值。The following example shows formatting numeric values by specifying a custom format. 此範例假設目前的日期為 2012 年 9 月 27 日。The example assumes that the current date is September 27, 2012. 如需有關這些自訂格式和其他自訂格式的詳細資訊,請參閱自訂數值格式字串For more information about these and other custom formats, see Custom Numeric Format Strings.

DECLARE @d DATETIME = GETDATE();  
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'  
       ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result';  

以下為結果集:Here is the result set.

DateTime Result  Custom Number Result  
--------------   --------------------  
27/09/2012       123-45-6789  

(1 row(s) affected)  

C.C. 數值類型的 FORMATFORMAT with numeric types

下列範例會從 AdventureWorks2012AdventureWorks2012 資料庫的 Sales.CurrencyRate 資料表傳回 5 個資料列。The following example returns 5 rows from the Sales.CurrencyRate table in the AdventureWorks2012AdventureWorks2012 database. EndOfDateRate 資料行會以 money 類型,儲存在資料表中。The column EndOfDateRate is stored as type money in the table. 在此範例中,資料行會以未格式化的狀態傳回,然後藉由指定 .NET Number 格式、General 格式和 Currency 格式類型進行格式化。In this example, the column is returned unformatted and then formatted by specifying the .NET Number format, General format, and Currency format types. 如需有關這些數值格式和其他數值格式的詳細資訊,請參閱標準數值格式字串For more information about these and other numeric formats, see Standard Numeric Format Strings.

SELECT TOP(5)CurrencyRateID, EndOfDayRate  
            ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number Format'  
            ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'  
            ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'  
FROM Sales.CurrencyRate  
ORDER BY CurrencyRateID;  

以下為結果集:Here is the result set.

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format  
-------------- ------------  --------------  --------------  ---------------  
1              1.0002        1.00            1.0002          $1.00  
2              1.55          1.55            1.5500          $1.55  
3              1.9419        1.94            1.9419          $1.94  
4              1.4683        1.47            1.4683          $1.47  
5              8.2784        8.28            8.2784          $8.28  

(5 row(s) affected)  

此範例會指定德文文化特性 (de-de)。This example specifies the German culture (de-de).

SELECT TOP(5)CurrencyRateID, EndOfDayRate  
      ,FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format'  
      ,FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'  
      ,FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'  
FROM Sales.CurrencyRate  
ORDER BY CurrencyRateID;  
CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format  
-------------- ------------  --------------  --------------  ---------------  
1              1.0002        1,00            1,0002          1,00 €  
2              1.55          1,55            1,5500          1,55 €  
3              1.9419        1,94            1,9419          1,94 €  
4              1.4683        1,47            1,4683          1,47 €  
5              8.2784        8,28            8,2784          8,28 €  

 (5 row(s) affected)  

D.D. 具有 time 資料類型的 FORMATFORMAT with time data types

FORMAT 會在這些情況下傳回 NULL,因為未逸出 .:FORMAT returns NULL in these cases because . and : are not escaped.

SELECT FORMAT(cast('07:35' as time), N'hh.mm');   --> returns NULL  
SELECT FORMAT(cast('07:35' as time), N'hh:mm');   --> returns NULL  

Format 會傳回格式化的字串,因為會逸出 .:Format returns a formatted string because the . and : are escaped.

SELECT FORMAT(cast('07:35' as time), N'hh\.mm');  --> returns 07.35  
SELECT FORMAT(cast('07:35' as time), N'hh\:mm');  --> returns 07:35  

另請參閱See Also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
STR (Transact-SQL)STR (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)