This article explains the usage of SQL Format function and performance comparison with 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.
We use this new function to get output in a specified format and Culture. It returns an NVARCHAR data type in the output.
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
- 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 Format types in .NET for more details
- 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
We use the following SQL CONVERT function to get output in [MM/DD/YYYY] format:
|
1 |
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.
We do not require format code in SQL FORMAT function. Let’s look at various examples of the FORMAT function:
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 |
|
|
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.
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 |
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 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:
|
1 2 3 4 5 6 |
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'; |
Number format using FORMAT function
|
1 2 3 4 5 6 |
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'; |
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:
|
1 2 3 4 |
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'; |
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.
For performance comparison, create a table and insert data into it:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE TestPerformance (ID INT, InputTime DATETIME DEFAULT GETDATE() ); Insert into Testperformance (ID) values (1) go 1000 |
Now, execute the following SQL queries:
Query1: Select all records from the TestPerfomance order by ID column:
1234SELECT *FROM TestPerformanceORDER BY id;GO 10Query 2: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:
1234SELECT CONVERT(DATE, InputTime)FROM TestPerformanceORDER BY id;GO 10Query 3: Select all records from TestPerfomance order by ID column and use convert function for the InputTime column:
1234SELECT CONVERT(CHAR(10), InputTime, 120)FROM TestPerformanceORDER BY id;GO 10Query4: Select all records from TestPerfomance order by ID column and use FORMAT function for the InputTime column:
1234SELECT FORMAT(InputTime, 'yyyy-MM-dd')FROM TestPerformanceORDER 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
To understand it better, let’s view this data in a graph:
Look at the graph for query 2, 3 and 4.
- We get high elapsed time for the query that uses SQL FORMAT function
- Queries that use the CONVERT function have better performance compare to FORMAT function
- 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
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023