sql学习指南
This article explains the usage of SQL Format function and performance comparison with SQL CONVERT.
本文介绍了SQL格式功能的用法以及与SQL CONVERT的性能比较。
介绍 (Introduction)
In the article SQL Convert Date functions and formats, we discussed the usage of SQL CONVERT function for converting date and time into multiple formats. We have a new function, SQL FORMAT, from SQL Server 2012.
在“ SQL Convert Date函数和格式”一文中 ，我们讨论了如何使用SQL CONVERT函数将日期和时间转换为多种格式。 我们有一个来自SQL Server 2012的新功能SQL FORMAT。
We use this new function to get output in a specified format and Culture. It returns an NVARCHAR data type in the output.
我们使用此新功能来获取指定格式和文化的输出。 它在输出中返回NVARCHAR数据类型。
SQL FORMAT函数的语法 (Syntax of SQL FORMAT Function)
FORMAT (value, format [, culture])
格式（值，格式[，文化]）
It has the following parameters:
它具有以下参数：
- Value: It is the value to do formatting. It should be in support of the data type format. You can refer to 值 ：是进行格式化的值。 它应该支持数据类型格式。 您可以参考Microsoft documentation for a list of supported data types and their equivalent data type Microsoft文档以获取受支持的数据类型及其等效数据类型的列表。
- Format: It is the required format in which we require the output. This parameter should contain a valid .NET format string in the NVARCHAR data type. We can refer to 格式 ：这是我们需要输出的必需格式。 此参数应包含NVARCHAR数据类型的有效.NET格式字符串。 我们可以参考Format types in .NET for more details .NET中的格式类型以获取更多详细信息
- Culture: It is an optional parameter. By default, SQL Server uses the current session language for a default culture. We can provide a specific culture here, but the .Net framework should support it. We get an error message in case of invalid Culture 文化 ：这是一个可选参数。 默认情况下，SQL Server使用当前会话语言作为默认区域性。 我们可以在此处提供特定的文化，但是.Net框架应该支持它。 如果文化无效，我们会收到一条错误消息
We use the following SQL CONVERT function to get output in [MM/DD/YYYY] format:
我们使用以下SQL CONVERT函数获取[MM / DD / YYYY]格式的输出：
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
As we know, we require format code in SQL Convert function for converting output in a specific format.
众所周知，我们需要SQL Convert函数中的格式代码才能将输出转换为特定格式。
We do not require format code in SQL FORMAT function. Let’s look at various examples of the FORMAT function:
我们在SQL FORMAT函数中不需要格式代码。 让我们看一下FORMAT函数的各种示例：
Format String and description | Query | Output |
d – It shows the day of the month from 1 through 31. | |
|
D – It gives a detailed output in Weekday, Month, Date, Year format. | |
|
f- It adds timestamp as well in the output of D parameter.it does not include seconds information. | |
|
F- It adds seconds (ss) information also in the output generated from f parameter. | |
|
g:- It gives output in MM/DD/YYYY hh: mm AM/PM. | |
|
G: Output format MM/DD/YYYY hh:mm: ss AM/PM. | |
|
M/m: Output format- Month date | |
|
O – Output format yyyy-mm-ddThh:mm:ss.nnnnnnn | |
|
R – Output format Day, dd Mon yyyy hh:mm:ss GMT | |
|
S : Output format yyyy-mm-ddThh:mm:ss | |
|
U : Output format yyyy-mm-dd hh:mm:ssz | |
|
U : Output format Day, Mon dd , yyyy hh:mm:ss AM/PM | |
|
T : Output format hh:mm:ss AM/PM | |
|
t : Output format hhLmm AM/PM | |
|
Y: Output format Mon yyyy | |
|
Output format – MM/dd/yy | |
|
Output format – MMdd/yyyy | |
|
Output format -yy.MM.dd | |
|
Output format yyyy.MM.dd | |
|
Output format -dd/MM/yy | |
|
Output format -dd/MM/yy | |
|
Output format – dd-MM-yyyy | |
|
Output format – dd MMM yy | |
|
Output format – dd MMM yyyy | |
|
Output format – MMM dd yyyy | |
|
Output format – HH:mm:ss | |
|
Output format – MMM d yyyy h:mm:ss | |
|
Output format – Dd MMM yyyy HH:mm:ss | |
|
Output format – yyyy-MM-dd HH:mm:ss | |
|
Output format – MM/dd/yy h:mm:ss tt | |
|
Output format – yy-M-d | |
|
Output format – M-d-yyyy | |
|
Output format – d-M-yyyy | |
|
Output format – d-M-yy | |
|
Output format – yyyy/M/d | |
|
Output format – MM.dd.yyyy | |
|
Output format – MMMM dd,yyyy | |
|
格式字符串和描述 | 询问 | 输出量 |
d –显示从1到31的月份。 | | |
D –以工作日，月份，日期，年份格式给出详细的输出。 | | |
f-它也在D参数的输出中添加时间戳。它不包含秒信息。 | | |
F-它也会在从f参数生成的输出中添加秒（ss）信息。 | | |
g：-以MM / DD / YYYY hh：mm AM / PM给出输出。 | | |
G：输出格式MM / DD / YYYY hh：mm：ss AM / PM。 | | |
| M / m：输出格式- 月份日期 | | |
| O –输出格式 yyyy-mm-ddThh：mm：ss.nnnnnnn | | |
R –输出格式Day，dd Mon yyyy hh：mm：ss GMT | | |
S：输出格式yyyy-mm-ddThh：mm：ss | | |
U：输出格式 yyyy-mm-dd hh：mm：ssz | | |
U：输出格式Day，Mon dd，yyyy hh：mm：ss AM / PM | | |
T：输出格式hh：mm：ss AM / PM | | |
t：输出格式hhLmm AM / PM | | |
| Y：输出格式 星期一yyyy | | |
输出格式– MM / dd / yy | | |
输出格式– MMdd / yyyy | | |
输出格式-yy.MM.dd | | |
输出格式yyyy.MM.dd | | |
输出格式-dd / MM / yy | | |
输出格式-dd / MM / yy | | |
| 输出格式 - dd-MM-yyyy | | |
| 输出格式 - dd MMM yy | | |
| 输出格式 - dd MMM yyyy | | |
| 输出格式 - MMM dd yyyy | | |
| 输出格式 - HH：mm：ss | | |
| 输出格式 - MMM d yyyy h：mm：ss | | |
| 输出格式 - Dd MMM yyyy HH：mm：ss | | |
| 输出格式 - yyyy-MM-dd HH：mm：ss | | |
| 输出格式 - MM / dd / yy h：mm：ss tt | | |
| 输出格式 - y | | |
| 输出格式 - md-yyyy | | |
| 输出格式 - dM-yyyy | | |
| 输出格式 - dM-yy | | |
| 输出格式 - yyyy / M / d | | |
| 输出格式 - MM.dd.yyyy | | |
| 输出格式 - MMMM dd，yyyy | |
使用文化SQL格式日期 (SQL Format Date using Culture)
In the previous section, we did not use the culture argument. As you know, it is an optional argument, so let’s see what difference it makes if we specify it in a query.
在上一节中，我们没有使用文化论点。 如您所知，它是一个可选参数，所以让我们看看如果在查询中指定它会产生什么区别。
In the below query, we see date format using d argument for different cultures. You can refer to the table for culture codes.
在下面的查询中，我们看到使用d参数表示不同文化的日期格式。 您可以参考下表中的文化代码 。
DECLARE @d DATETIME= '03/02/2020'
SELECT FORMAT(@d, 'd', 'en-US') AS 'US English format',
FORMAT(@d, 'd', 'en-gb') AS 'Great Britain English format',
FORMAT(@d, 'd', 'de-de') AS 'German format',
FORMAT(@d, 'd', 'zh-cn') AS 'Simplified Chinese (PRC) format',
FORMAT(@d, 'd', 'hi-IN') AS 'India format',
FORMAT(@d, 'd', 'ru-RU') AS 'Russian format',
FORMAT(@d, 'd', 'gl-ES') AS 'Galician (Spain) format';
Similarly, if we change the format from d to f in the above query, it gives the following output:
同样，如果在上面的查询中将格式从d更改为f，则将提供以下输出：
DECLARE @d DATETIME= '03/02/2020'
SELECT FORMAT(@d, 'f', 'en-US') AS 'US English format',
FORMAT(@d, 'f', 'en-gb') AS 'Great Britain English format',
FORMAT(@d, 'f', 'de-de') AS 'German format',
FORMAT(@d, 'f', 'zh-cn') AS 'Simplified Chinese (PRC) format',
FORMAT(@d, 'f', 'hi-IN') AS 'India format',
FORMAT(@d, 'f', 'ru-RU') AS 'Russian format',
FORMAT(@d, 'f', 'gl-ES') AS 'Galician (Spain) format';
使用文化SQL格式货币 (SQL Format Currency using Culture)
We can also format currency using this function. Suppose you manage an online shopping portal where customers from a different country come for shopping. You want to display the product prices in their currency. In the following query, we use the FORMAT function for showing pricing with a currency symbol:
我们也可以使用此功能格式化货币。 假设您管理一个在线购物门户，来自不同国家的顾客来此购物。 您想以其货币显示产品价格。 在以下查询中，我们使用FORMAT函数使用货币符号显示定价：
DECLARE @ProductCost INT= 12345;
SELECT FORMAT(@ProductCost, 'c', 'en-US') AS 'USA Currency',
FORMAT(@ProductCost, 'c', 'ru-RU') AS 'Russian Currency',
FORMAT(@ProductCost, 'c', 'hi-IN') AS 'Indian Currency',
FORMAT(@ProductCost, 'c', 'de-DE') AS 'Euro Currency',
FORMAT(@ProductCost, 'c', 'en-gb') AS 'Britain Currency';
使用FORMAT功能的数字格式 (Number format using FORMAT function )
DECLARE @ProductCost INT= 12345;
SELECT FORMAT(@ProductCost, 'N', 'en-US') AS 'USA Number format',
FORMAT(@ProductCost, 'N', 'ru-RU') AS 'Russian Number format',
FORMAT(@ProductCost, 'N', 'hi-IN') AS 'Indian Number format',
FORMAT(@ProductCost, 'N', 'de-DE') AS 'Euro Number format',
FORMAT(@ProductCost, 'N', 'en-gb') AS 'Britain Number format';
在SQL FORMAT函数中转义冒号和句点 (Escaping Colons and Periods in SQL FORMAT function)
We should avoid colons and periods in this function, and it is adhering to the .NET CLR rules as well. We can use colons and period as the second parameter, and the first parameter should be a backslash. In the following example, let us see the second format statement skips the colon in the time specified:
我们应该在此函数中避免冒号和句点，并且它也遵循.NET CLR规则。 我们可以使用冒号和句点作为第二个参数，第一个参数应为反斜杠。 在下面的示例中，让我们看到第二个format语句在指定的时间内跳过了冒号：
SELECT
CAST('10:20' AS time) AS 'Unformatted Data',
FORMAT(CAST('10:20' AS time), N'hh.mm') AS 'Unescaped Time',
FORMAT(CAST('10:20' AS time), N'hh\.mm') AS 'Escaped Time';
SQL FORMAT和SQL CONVERT函数的性能比较 (Performance comparison of SQL FORMAT and SQL CONVERT function)
We explored the use cases of FORMAT function. You might think we should stop using the SQL CONVERT function and start using the SQL FORMAT. Wait! Let’s make a comparison of both SQL FORMAT and SQL CONVERT.
我们探讨了FORMAT函数的用例。 您可能认为我们应该停止使用SQL CONVERT函数，而开始使用SQL FORMAT。 等待！ 让我们比较一下SQL FORMAT和SQL CONVERT。
For performance comparison, create a table and insert data into it:
为了进行性能比较，请创建一个表并将数据插入其中：
CREATE TABLE TestPerformance
(ID INT,
InputTime DATETIME DEFAULT GETDATE()
);
Insert into Testperformance (ID) values (1)
go 1000
Now, execute the following SQL queries:
现在，执行以下SQL查询：
Query1: Select all records from the TestPerfomance order by ID column:
Query1：从TestPerfomance的ID列中选择所有记录：
SELECT * FROM TestPerformance ORDER BY id; GO 10Query 2: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:
查询2：从TestPerfomance的ID列中选择所有记录，并在InputTime列中使用convert函数：
SELECT CONVERT(DATE, InputTime) FROM TestPerformance ORDER BY id; GO 10Query 3: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:
查询3：按ID列从TestPerfomance订单中选择所有记录，并为InputTime列使用convert函数：
SELECT CONVERT(CHAR(10), InputTime, 120) FROM TestPerformance ORDER BY id; GO 10Query4: Select all records from TestPerfomance order by ID column and use FORMAT function for the InputTime column:
Query4：从TestPerfomance的ID列中选择所有记录，并为InputTime列使用FORMAT函数：
SELECT FORMAT(InputTime, 'yyyy-MM-dd') FROM TestPerformance ORDER BY id; GO 10
We can use DMV sys.dm_exec_query_stats and sys.dm_exec_sql_text to get the performance comparison data of the select statements we executed above.
我们可以使用DMV sys.dm_exec_query_stats和sys.dm_exec_sql_text获取上面执行的select语句的性能比较数据。
SELECT
[t] = CONVERT(CHAR(255), t.[text]),
s.total_elapsed_time,
avg_elapsed_time = CONVERT(DECIMAL(12,2),s.total_elapsed_time / 5.0),
s.total_worker_time,
avg_worker_time = CONVERT(DECIMAL(12,2),s.total_worker_time / 5.0),
s.total_clr_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) AS t
WHERE
t.[text] LIKE N'%Testperformance%'
and t.[text] not like '%dm_exec_query_stats%'
ORDER BY s.last_execution_time;
We get the following output from the DMV:
我们从DMV获得以下输出：
To understand it better, let’s view this data in a graph:
为了更好地理解它，让我们在图形中查看此数据：
Look at the graph for query 2, 3 and 4.
查看查询2、3和4的图形。
- We get high elapsed time for the query that uses SQL FORMAT function 对于使用SQL FORMAT函数的查询，我们花费了大量的时间
- Queries that use the CONVERT function have better performance compare to FORMAT function 与FORMAT函数相比，使用CONVERT函数的查询具有更好的性能
- We also see total_clr_time for the query with FORMAT function while it is zero for CONVERT function queries because the format function uses .Net CLR runtime 对于FORMAT函数的查询，我们也看到total_clr_time，而对于CONVERT函数查询，它为零，因为format函数使用.Net CLR运行时
结论 (Conclusion)
SQL FORMAT function is useful to convert the dates, time, number, currency in a specified format. We should use it when we require locale-aware changes only as it might cause performance issues. We should use the SQL CONVERT function for all other cases. If you plan to use it in production, I would recommend doing a thorough performance testing for your workload.
SQL FORMAT函数可用于以指定格式转换日期，时间，数字，货币。 我们仅在需要区域设置感知更改时才应使用它，因为它可能会导致性能问题。 对于所有其他情况，我们应该使用SQL CONVERT函数。 如果您打算在生产中使用它，我建议对您的工作负载进行彻底的性能测试。
翻译自: https://www.sqlshack.com/a-comprehensive-guide-to-the-sql-format-function/
sql学习指南
扫一扫
327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
