SQL 格式化

此 SQL Server 字符串函数用于以给定方式格式化指定值，或使用区域设置格式化日期和数字。Format 函数的语法是

Format(Value, Format, Culture)
  • 值： 请指定一个有效的支持的数据类型的表达式。
  • 格式： 请指定一个有效的 .NET Framework 字符串。
  • 区域设置： 这是一个可选参数，用于区域或特定国家的更改。

SQL 格式化日期示例

在此字符串函数示例中，我们首先声明了一个 Datetime 变量并将 GETDATE() 赋值给它。在这里，我们将使用该函数以不同的格式返回日期。我建议您参考标准日期时间格式文章以理解我们在本示例中使用的字符串格式。

DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'd', 'en-US' ) AS 'Result 1',  FORMAT(@Vardate, 'D', 'en-US' ) AS 'Result 2'
SELECT FORMAT(@Vardate, 'f', 'en-US' ) AS 'Result 3',  FORMAT(@Vardate, 'F', 'en-US' ) AS 'Result 4'
SELECT FORMAT(@Vardate, 'g', 'en-US' ) AS 'Result 5',  FORMAT(@Vardate, 'G', 'en-US' ) AS 'Result 6'
SELECT FORMAT(@Vardate, 'm', 'en-US' ) AS 'Result 7',  FORMAT(@Vardate, 'M', 'en-US' ) AS 'Result 8'
SELECT FORMAT(@Vardate, 'O', 'en-US' ) AS 'Result 9',  FORMAT(@Vardate, 'R', 'en-US' ) AS 'Result 10'
SELECT FORMAT(@Vardate, 's', 'en-US' ) AS 'Result 11', FORMAT(@Vardate, 'S', 'en-US' ) AS 'Result 12'
SELECT FORMAT(@Vardate, 't', 'en-US' ) AS 'Result 13', FORMAT(@Vardate, 'T', 'en-US' ) AS 'Result 14'
SELECT FORMAT(@Vardate, 'u', 'en-US' ) AS 'Result 15', FORMAT(@Vardate, 'U', 'en-US' ) AS 'Result 16'
SELECT FORMAT(@Vardate, 'Y', 'en-US' ) AS 'Result 17'
SQL Format Date Example 1

SQL Server 使用区域设置格式化日期

在此示例中，我们将使用第三个参数，即区域设置。通过此参数，您可以以本地语言显示月份名称或日期名称，例如日语、中文、印地语、俄语、韩语等的天名称。

DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'dd', 'en-US' ) AS 'Result 1',  FORMAT(@Vardate, 'dddd', 'hi-IN' ) AS 'Result 2'
SELECT FORMAT(@Vardate, 'd', 'de-DE' ) AS 'Result 3',  FORMAT(@Vardate, 'dddd', 'ru-RU' ) AS 'Result 4'
SELECT FORMAT(@Vardate, 'M', 'en-US' ) AS 'Result 5',  FORMAT(@Vardate, 'MMMM', 'hi-IN' ) AS 'Result 6'
SELECT FORMAT(@Vardate, 'MM', 'de-DE' ) AS 'Result 7',  FORMAT(@Vardate, 'MMMM', 'ru-RU' ) AS 'Result 8'
SELECT FORMAT(@Vardate, 'yy', 'en-US' ) AS 'Result 9',  FORMAT(@Vardate, 'y', 'hi-IN' ) AS 'Result 10'
SELECT FORMAT(@Vardate, 'yyyy', 'de-DE' ) AS 'Result 11',  FORMAT(@Vardate, 'y', 'ru-RU' ) AS 'Result 12'
using Culture Example 2

在本节中，我们将定义自定义日期时间格式。通过此，您可以确定用于显示日期和时间的自定义格式。

DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'dd/mm/yyyy') AS 'date in dd/mm/yyyy Format'  
SELECT FORMAT(@Vardate, 'dd/mm/yy') AS 'date in dd/mm/yy Format' 
SELECT FORMAT(@Vardate, 'd/m/yy') AS 'date in d/m/yy Format' 
SELECT FORMAT(@Vardate, 'mm/dd/yyyy') AS 'date in mm/dd/yyyy Format' 
SELECT FORMAT(@Vardate, 'MMMM dddd yyyy hh:mm:ss:mmmm') AS 'date in MMMM dddd yyyy hh:mm:ss:mmmm Format' 
SELECT FORMAT(@Vardate, 'MMMM yyyy, dddd hh:mm:ss:mmmm') AS 'date in MMMM yyyy, dddd hh:mm:ss:mmmm Format'
Changing Custom Date Example 3

SQL 使用区域设置格式化货币

在这种情况下，我们将根据指定的区域设置格式化货币值。

DECLARE @Sales INT = 3325 
SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'USA Currency'
SELECT FORMAT(@Sales, 'c', 'ru-RU' ) AS 'Russian Currency'
SELECT FORMAT(@Sales, 'c', 'hi-IN' ) AS 'Indian Currency'
SELECT FORMAT(@Sales, 'c', 'de-DE' ) AS 'Indian Currency'
Format Currency using Culture Example 4

在此示例中，我们使用此功能来格式化货币。使用此方法，您可以轻松地在货币或值前显示国家的货币符号。

DECLARE @Sales DECIMAL(8, 4) = 3325.2569 
SELECT FORMAT(@Sales, 'c' ) AS 'Result 1'
SELECT FORMAT(@Sales, 'c0' ) AS 'Result 2'
SELECT FORMAT(@Sales, 'c1' ) AS 'Result 3'
SELECT FORMAT(@Sales, 'c2' ) AS 'Result 4'
SELECT FORMAT(@Sales, 'c3' ) AS 'Result 5'
SELECT FORMAT(@Sales, 'c4' ) AS 'Result 6'
Change Currency to Country Specific Example 5

格式化货币小数

在此示例中，我们将使用此功能以货币格式化小数位数。

DECLARE @Sales DECIMAL(8, 4) = 3325.2569 
SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'Result 1'
SELECT FORMAT(@Sales, 'c0', 'hi-IN' ) AS 'Result 2'
SELECT FORMAT(@Sales, 'c1', 'ru-RU') AS 'Result 3'
SELECT FORMAT(@Sales, 'c2', 'fr-FR' ) AS 'Result 4'
SELECT FORMAT(@Sales, 'c3', 'de-DE') AS 'Result 5'
SELECT FORMAT(@Sales, 'c4', 'zh-CN') AS 'Result 6'
Decimals Example 6

使用区域设置

在此示例中，我们将使用该函数来格式化数字。区域设置指定了数字格式。我的意思是，一些国家使用逗号分隔千位，而另一些国家使用千位分隔万位等。

DECLARE @Number DECIMAL(11, 4) = 1453325.2569 
SELECT FORMAT(@Number, 'N', 'en-US') AS 'Result 1'
SELECT FORMAT(@Number, 'N', 'en-IN' ) AS 'Result 2'
SELECT FORMAT(@Number, 'N', 'ru-RU') AS 'Result 3'
SELECT FORMAT(@Number, 'N', 'fr-FR' ) AS 'Result 4'
SELECT FORMAT(@Number, 'N', 'de-DE') AS 'Result 5'
SELECT FORMAT(@Number, 'N', 'zh-CN') AS 'Result 6'
Restrict the Decimal Numbers using Culture Argument Example 7

格式化数字

在此示例中，我们将格式化数字。要格式化数字，您可以使用 # 符号。或者，您也可以使用 0 来指定小数位的数量。

DECLARE @Number DECIMAL(10,4) = 945354.1295 
DECLARE @Num INT = 945332534 
SELECT FORMAT(@Number, '#') AS 'Result 1'
SELECT FORMAT(@Number, '#,###' ) AS 'Result 2'
SELECT FORMAT(@Number, '#.00') AS 'Result 3'
SELECT FORMAT(@Number, '#,###.000' ) AS 'Result 4'
SELECT FORMAT(@Num, '###-##-####') AS 'Result 5'
Format Numbers Example 8