SQL Server FORMAT Function for Dates, Numbers and SQL Server Agent Dates

By:   |   Updated: 2021-10-28   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | > Dates


Since Microsoft SQL Server 2012, we have a new function called FORMAT(), it is very powerful in formatting datetime and numeric values, what are some good uses of this function to be used in daily DBA work?


The SQL Server T-SQL FORMAT() function is in essence a CLR type system function, and as such, the .Net formatting rules apply to this function. This function returns a nvarchar value (or null value).

In my daily DBA work, this function provides lots of convenience that otherwise need some nasty T-SQL conversion and string manipulation work to get the formats I need.

Using SQL Server FORMAT to return Deterministic Week Day

In one of my DBA alert emails, I need to include the weekday in one column, something like the following:

EnrollDate WeekDay NewMembers
2019-01-01 Thu 10
2019-01-03 Sat 24
2019-01-11 Fri 12
2019-02-01 Fri 18
2019-02-04 Mon 30

Figure 1 - Report

Assume my source table and data is like the following:

-- tested in SQL Server 2016
use TempDB

drop table if exists dbo.NewAccount;
create table dbo.NewAccount(EnrollDate date, NewMembers int);

insert into dbo.NewAccount(EnrollDate, NewMembers)
values('2019-01-01', 10)
,('2019-01-03', 24)
,('2019-01-11', 12)
,('2019-02-01', 18)
,('2019-02-04', 30);

If we want to get the data displayed as in the [Figure 1-Report], we need to calculate [WeekDay] which is a computed column based on the [EnrollDate] column.

In T-SQL, we have a date function, datepart(), that can tell the weekday, like the following:

declare @dt date = '2019-June-08';
select datepart(dw, @dt) -- returns 7

But the problem with this approach is that the return value of datepart(dw, '<date>') is impacted by the datefirst setting as shown below:

set datefirst <N> -- where N is between 1 and 7

But to get rid of the ambiguity, we can use the FORMAT() function as follows:

declare @dt date='2019-June-08';
select FORMAT(@dt, 'ddd') -- returns Sat

So, for our example mentioned above, we can use the following query to get the desired result:

select EnrollDate, WeekDay=format(EnrollDate, 'ddd'), NewMembers
from tempdb.dbo.NewAccount;

The result is:

Get weekday from date value

Use SQL Server FORMAT to get Various Numeric Display Formats

There are many requirements on how a numeric data type is displayed, such as a thousand separator every 3 digits, a percentage sign, a currency prefix or a different format when minus value is encountered. The following table lists some most commonly used numeric value formats.

ID Format Result Note
1 SELECT FORMAT(123456.789, 'C4') $123,456.7890 currency
2 SELECT FORMAT(0.1234, '0.00%')
SELECT FORMAT(0.1234, 'P2')
12.34% 12.34% Percentage sign %
3 SELECT FORMAT(0.12345, '0.00‰') 123.45‰ Per mille sign ‰
4 SELECT FORMAT(123456789, '#,#');
SELECT FORMAT(123456789, '0,#');
Thousand separator
5 SELECT FORMAT(255, 'X3') ; 0FF Convert to HEX string
6 SELECT FORMAT(123, '#;(#);[zero]')
SELECT FORMAT(-123, '#;(#);[zero]')
SELECT FORMAT(0, '#;(#);[zero]')
; is section operator, it defines separate format strings for positive, negative, and zero numbers, so in the example, for negative number, it will have () around the number while for zero value, it will show a string, i.e. [zero]

A practical use of format is shown in the following example, i.e. previously when I need to check row counts for tables in a database, I find it is very inconvenient to figure what very high numbers are without the comma separator, especially when you have hundreds of millions of rows or more. The following code lists the rows of each table in database [WideWorldImporter].

use WideWorldImporter

select [table]=schema_name(t.schema_id)+'.'+t.name 
, [rows]=sum(p.rows), delimited_rows= format(sum(p.rows), '#,#')
from sys.tables t
inner join sys.partitions p
on t.object_id = p.object_id
and p.index_id < 2
group by schema_name(t.schema_id)+'.'+t.name 
orderby [rows]desc;
Compare rows vs delimited_rows

The [delimited_rows] column has a comma for every 3 digits, which makes reading much easier than the [rows] column.

Use SQL Server FORMAT to Display SQL Server Agent Job Run Duration

SQL Server job history table dbo.sysjobhistory has a column named [run_duration] as shown below:

Run_duration column in dbo.sysjobhistory

This is an integer column, when I need to generate a report, I really want this to be HH:MM:SS format, for example if run_duration is 1, I want it to be 00:00:01, more examples are as listed in the following table.

Run_value Expected Display Format
12 00:00:12
123 00:01:23
1234 00:12:34
12345 01:23:45
123456 12:34:56
251234 1d+01:12:34

So how can we do achieve this?

The trick here is that I will use a non-documented yet simple function in msdb database, dbo.agent_datetime, to compose a datetime value by inputting a dummy value, like 20000101, for @date parameter and use the [run_duration] column for @time parameter, and then use FORMAT() to return the HH:MM:SS formatted result.

dbo.agent_datetime function

To illustrate, I will create a table and populate it with some data simulating the [run_duration] column in dbo.sysjobhistory table.

use tempdb

drop table if exists dbo.jobhistory; -- for sql server 2016 and above version
create table dbo.jobhistory(id int identity primary key, run_duration int);

insert into dbo.jobhistory(run_duration) 

With the following code, I can convert the [run_duration] from HHMMSS int value to HH:MM:SS string value with the following syntax:

select *
, case when run_duration / 240000 =0  
      then format(msdb.dbo.agent_datetime(20000101, run_duration), 'HH:mm:ss')
  else cast(run_duration/240000 as varchar(2))+'d+' + format(msdb.dbo.agent_datetime(20000101, run_duration%240000), 'HH:mm:ss')
end as [formatted_run_duration]
from dbo.jobhistory;

The result is as follows, exactly what I want:



In this tip, we have revisited the FORMAT() function introduced in SQL Server 2012, and listed a few practical examples that otherwise are difficult to implement. The function provides convenience for daily DBA report work and when used properly, the function helps to make code concise, easy to understand and simple to maintain.

I did not discuss about the culture parameter used in the FORMAT() function. This culture parameter can be very useful in some culture related format work, such as currency sign, weekday name, date format (i.e. year/month/day or month/day/year, etc.). In some companies with offices across world, we can build some queries that will generate culture-compatible results.

Next Steps

You can read some other good MSSQL tutorials on MSSQLTips.com talking about the same topic.

Please share your own findings or creative usage of this function.

There is also a very interesting article discussing some shortcomings of the FORMAT() function.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-28

Comments For This Article

Friday, September 29, 2023 - 11:59:08 AM - Joe Celko Back To Top (91611)
We never intended SQL to be used this way. This is why we pass the standards, only the ISO 8601 format (yyyy-mm-dd HH:mm:ss.sssssss) is allowed for dates and times. Display formatting is supposed to be done in a presentation layer of the tiered architecture.

Sunday, August 11, 2019 - 6:05:33 PM - Jeff Moden Back To Top (82029)

@Jeffrey Yao,

You say that the performance issue is "well mentioned" but, unless I'm missing it, I didn't see anything in the article mentioning it.  Maybe the article should be updated so that readers know the function is a potential performance issue with a large data set. The performance issues with FORMAT are also not as well known because people keep advocating its use without mentioning the performance problem.

As for people that make the excuse that "there are many cases where performance is not a concern", if you always write code like it was a concern, you'll never be surprised when it does become a concern.  Performance is only secondary to correct functionality and, especially with as large as tables are nowadays, it's a very, very close second.  It's just not that difficult to make such code much more bullet-proof, especially when it comes to scalability.

Ignoring for a moment that people also preach that formatting should be left to the front end or reporting code (and, normally, it should be), I agree that it's sometimes a bit more complicated (and it's just a "bit") to do the formatting without using the FORMAT function, but it's well worth it because that means that the extremely performance challenged FORMAT function will never be the cause of such performance issues because you're always using much faster methods.

I'm not advocating their use but even scalar functions are only ~7 times slower than inline code.  The FORMAT function is typically 43 times slower.

Tuesday, August 6, 2019 - 2:19:41 PM - jeff_yao Back To Top (81982)

Thanks for the comment, @jeff Moden, yes, the performance issue is well mentioned and (I believe) well understood by the community. But there are many cases where performance is not a concern, while convenience is, like when I check the database file size (mulitple files with a few GBs to several hundred GBs) and I want to have the number separated by a comma every 3 digits. 

For discussion purpose, I always have some suspicion that in some cases, FORMAT() may be better than t-sql, for example, separate large numbers with comma every 3 digits, or formating a numeric with percentage %. I never test it but just of gut feeling, it seems the t-sql is complex to achieve the same result.

Tuesday, August 6, 2019 - 10:56:03 AM - Jeff Moden Back To Top (81976)

As usual for you, Jeffrey, great article.  The one thing that is missing is a performance test and that's really important for this function because it's been proven many time by myself and many others that the FORMAT function in SQL Server takes 43 times longer than CONVERT.  There's no way that I'd use the format function in SQL Server.

get free sql tips
agree to terms