数据格式化SQL函数FORMAT()-本牛千智丨专注WorkFine开发

在WorkFine中此函数多用于视图和决策中

新版决策出来了，想要计算直接显示百分比就需要使用此类转换函数。

本文目录 隐藏

FORMAT()函数介绍

FORMAT() 函数是 SQL Server 中一个将日期时间、数字等值，按指定格式和区域语言，转换为易于阅读的字符串的强大工具。你需要同时传入值、目标格式和可选的区域设置参数。

📖 快速入门

-- 格式化日期（标准格式）
DECLARE @d DATE = '2024-08-09';
SELECT 
    FORMAT(@d, 'd', 'en-US') AS '美国英语 (短日期)',      -- 返回 8/9/2024
    FORMAT(@d, 'D', 'zh-CN') AS '简体中文 (长日期)';      -- 返回 2024年8月9日

-- 格式化数字（标准格式）
SELECT 
    FORMAT(1234567.89, 'N') AS '数字格式',               -- 返回 1,234,567.89
    FORMAT(1234567.89, 'C', 'en-US') AS '货币格式';       -- 返回 $1,234,567.89

用法:FORMAT(值、格式、区域性)
FORMAT( value , format [ , culture ] )

📝 参数说明

该方法接受三个参数，如上所述，如下所述:

值:是做格式化的值。它应该支持数据类型格式。
格式:是我们要求输出的要求格式。
文化:可选参数。默认情况下，SQL Server 使用当前会话语言作为默认区域性。我们可以在这里提供特定的文化，但是。Net 框架应该支持它。如果区域性无效，我们会收到一条错误消息

返回:返回用指定格式和可选区域性格式化的值。

🔢 数字格式化

同样支持基于 .NET 的标准和自定义格式。

标准格式示例：

SELECT 
    FORMAT(1234567.89, 'N')   AS '默认数字格式', -- Output: 1,234,567.89
    FORMAT(1234567.89, 'N2')  AS '两位小数',      -- Output: 1,234,567.89
    FORMAT(0.12345, 'P')      AS '百分比格式',    -- Output: 12.35%
    FORMAT(1234.56, 'C', 'en-US')  AS '美元货币', -- Output: $1,234.56
    FORMAT(1234.56, 'C', 'de-DE')  AS '欧元货币', -- Output: 1.234,56 €
    FORMAT(255, 'X')          AS '十六进制';      -- Output: FF

自定义格式示例：

SELECT 
    FORMAT(123456789.34, '##-##-#####.##')  AS '自定义数字', -- Output: 12-34-56789.34
    FORMAT(123.4, '0000000')                AS '补零',        -- Output: 0000123
    FORMAT(12345, 'N0')                     AS '千位分隔符';  -- Output: 12,345

科学计数显示

format(计数排除重复(字段),'E')

千分号显示

在SQL Server中，可以使用FORMAT函数来添加千分号。FORMAT函数允许你指定数字的显示格式，包括千分位分隔符。

下面是一个使用FORMAT函数为数字添加千分号的例子：

SELECT FORMAT(1234567.89, 'N0') AS FormattedNumber; --注意是0不是O
--FormattedNumber
--1,234,567

计算值百分比显示

FORMAT(字段, 'P')

模板视图应用实例

如果你想要保留小数点后的位数，可以在格式字符串中指定精度，例如使用’N2’来显示两位小数：

SELECT FORMAT(1234567.89, 'N2') AS FormattedNumber;
-- 输出1,234,567.89

请注意，FORMAT函数在SQL Server 2012及更高版本中可用。如果你使用的是更早的版本，可能需要考虑其他方法，如使用STR函数配合字符串函数来实现类似的效果。

📅 日期和时间格式化

你可以使用基于 .NET Framework 的标准或自定义格式字符串。

标准格式示例：

DECLARE @now DATETIME = GETDATE();

SELECT 
    FORMAT(@now, 'd')  AS '短日期模式 (d)',      -- Output: 2023/10/18
    FORMAT(@now, 'D')  AS '长日期模式 (D)',      -- Output: 2023年10月18日, 星期三
    FORMAT(@now, 't')  AS '短时间模式 (t)',      -- Output: 11:04
    FORMAT(@now, 'T')  AS '长时间模式 (T)',      -- Output: 11:04:44
    FORMAT(@now, 'f')  AS '完整日期/时间 (f)',   -- Output: 2023年10月18日 11:04
    FORMAT(@now, 'F')  AS '完整日期/时间 (F)',   -- Output: 2023年10月18日 11:04:44
    FORMAT(@now, 's')  AS '可排序日期/时间 (s)', -- Output: 2023-10-18T11:04:44
    FORMAT(@now, 'u')  AS '通用可排序日期/时间 (u)',-- Output: 2023-10-18 11:04:44Z
    FORMAT(@now, 'M')  AS '月日模式 (M)',        -- Output: 10月 18
    FORMAT(@now, 'o')  AS '往返日期/时间模式 (o)';-- Output: 2023-10-18T11:04:44.2330000

自定义格式示例：

DECLARE @now DATETIME2 = GETDATE();
SELECT 
    FORMAT(@now, 'yyyy-MM-dd')          AS '自定义日期',     -- Output: 2023-10-18
    FORMAT(@now, 'dd/MM/yyyy')          AS '欧洲样式日期',   -- Output: 18/10/2023
    FORMAT(@now, 'MMMM dd, yyyy (hh:mm tt)') AS '详细日期时间', -- Output: 十月 18, 2023 (11:04 上午)
    FORMAT(@now, 'hh:mm:ss.fff')        AS '高精度时间';      -- Output: 11:04:44.233

日期转年月显示

FORMAT([SalesData].[SaleDate], 'yyyyMM')

还有另外一种方法

 CAST(DATEPART(year, GETDATE()) AS varchar) + RIGHT('0' + CAST(DATEPART(month, GETDATE()) AS varchar), 2)

🌐 区域设置 (Culture)

culture 参数是 FORMAT() 函数的一大亮点，能让你轻松适配不同地区的习惯。

DECLARE @price DECIMAL(18,2) = 1234567.89;

SELECT 
    FORMAT(@price, 'C', 'en-US') AS '美国英语', -- Output: $1,234,567.89
    FORMAT(@price, 'C', 'zh-CN') AS '简体中文'; -- Output: ¥1,234,567.89

日期显示同样支持区域设置：

DECLARE @dt DATE = '2024-08-09';
SELECT 
    FORMAT(@dt, 'D', 'en-AU')  AS '澳大利亚英语', -- Output: Friday, 9 August 2024
    FORMAT(@dt, 'D', 'es-ES')  AS '西班牙语';     -- Output: viernes, 9 de agosto de 2024

⚔️ 性能考量与建议

虽然 FORMAT() 提供了极大的便利，但也需注意其性能特点。

性能问题：FORMAT() 函数基于 .NET CLR 实现，处理大量数据时，开销显著高于 CONVERT() 函数。在几百万行数据的测试中，语句耗时可能相差3到40倍不等。
SQL Server 2012 引入：该函数从 SQL Server 2012 版本开始引入。

性能对比示例

-- 低效写法（大量数据时）
SELECT FORMAT(SalesDate, 'yyyyMMdd') FROM LargeSalesTable;

-- 推荐替代方案（大量数据时）
SELECT CONVERT(char(8), SalesDate, 112) FROM LargeSalesTable;

因此，一个有用的原则是：将 FORMAT() 用于最终呈现（少量数据），而将 CONVERT()/CAST() 用于数据处理（大量数据）。