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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

返回以指定的格式和可选的区域性格式化的值。Returns a value formatted with the specified format and optional culture. 使用 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 ] )  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

valuevalue
支持格式化的数据类型的表达式。Expression of a supported data type to format. 有关有效类型的列表,请参阅下面“备注”部分中的表。For a list of valid types, see the table in the following Remarks section.

format format
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."

区域性culture
指定区域性的可选 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 ServerSQL Server 显式支持的语言 。culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL ServerSQL Server. 如果 culture 参数无效,FORMAT 将引发错误 。If the culture argument is not valid, FORMAT raises an error.

返回类型Return Types

nvarchar 或 Null nvarchar or null

返回值的长度由 format 确定 。The length of the return value is determined by the format.

备注Remarks

FORMAT 将返回 NULL 错误,而不是非 valid 的 culture 。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 公共语言运行时 (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. 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
NumericNumeric bigintbigint Int64Int64
NumericNumeric intint Int32Int32
NumericNumeric smallintsmallint Int16Int16
NumericNumeric tinyinttinyint ByteByte
NumericNumeric Decimaldecimal SqlDecimalSqlDecimal
NumericNumeric numericnumeric SqlDecimalSqlDecimal
NumericNumeric FLOATfloat DoubleDouble
NumericNumeric realreal SingleSingle
NumericNumeric smallmoneysmallmoney DecimalDecimal
NumericNumeric moneymoney DecimalDecimal
日期和时间Date and Time datedate 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 DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'  
      ,FORMAT( @d, 'd', 'en-gb' ) 'Great Britain English'  
      ,FORMAT( @d, 'd', 'de-de' ) 'German'  
      ,FORMAT( @d, 'd', 'zh-cn' ) 'Simplified Chinese (PRC)';  
  
SELECT FORMAT( @d, 'D', 'en-US' ) 'US English'  
      ,FORMAT( @d, 'D', 'en-gb' ) 'Great Britain English'  
      ,FORMAT( @d, 'D', 'de-de' ) 'German'  
      ,FORMAT( @d, 'D', 'zh-cn' ) 'Chinese (Simplified PRC)';  

下面是结果集:Here is the result set.

US English  Great Britain English German     Simplified Chinese (PRC)  
----------  --------------------- ---------- ------------------------  
11/22/2020  22/11/2020            22.11.2020 2020/11/22 
  
US English                  Great Britain English  German                      Chinese (Simplified PRC)  
--------------------------- ---------------------- --------------------------  ---------------------------------------  
Sunday, November 22, 2020   22 November 2020       Sonntag, 22. November 2020  2020年11月22日  
  

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 DATE = GETDATE();  
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date'  
       ,FORMAT(123456789,'###-##-####') AS 'Custom Number';  

下面是结果集:Here is the result set.

Date        Custom Number  
----------  -------------  
22/11/2020  123-45-6789  
  

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 数字格式、常规格式和货币格式类型进行格式化。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  
  

此示例指定了德语区域性 (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 €  
  

D.D. 时间数据类型的 D. 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  

Format 返回指定了 AM 或 PM 的格式化当前时间Format returns a formatted current time with AM or PM specified

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt'); -- returns 03:46 PM
SELECT FORMAT(SYSDATETIME(), N'hh:mm t'); -- returns 03:46 P

Format 返回显示 AM 的指定时间Format returns the specified time, displaying AM

select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') -- returns 01:00 AM
select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm t')  -- returns 01:00 A

Format 返回显示 PM 的指定时间Format returns the specified time, displaying PM

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm tt') -- returns 02:00 PM
select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm t') -- returns 02:00 P

Format 返回采用 24 小时格式的指定时间Format returns the specified time in 24h format

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'HH:mm') -- returns 14:00

另请参阅See Also