Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2019-08-06   |   Comments (3)   |   Related: More > Dates

Problem

Since 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?

Solution

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
GO

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

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);
go

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 numeric data are 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,#');
123,456,789
123,456,789
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]')
123
(123)
[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
GO

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
GO

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);
go

insert into dbo.jobhistory(run_duration) 
values(1),(12),(123),(1234),(12345),(123456),(253456);
go

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

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:

formatted_run_duration

Summary

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 tips 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.



Last Updated: 2019-08-06


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

@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 06, 2019 - 2:19:41 PM - jeff_yao Back To Top

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 06, 2019 - 10:56:03 AM - Jeff Moden Back To Top

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.


Learn more about SQL Server tools