T-SQL Tips: Solving FORMAT function woes for time datatype

Impetus

The purpose of this blog is to clarify the confusion regarding the behavior of FORMAT function with the time datatype in SQL Server

Scenario

It all started with a discussion that happened in one of the groups. 

A person had posted a question asking about reason for the weird behavior of FORMAT function while applied on various date and time related datatypes in SQL 2012.

The illustration is given below

declare @t time ='20151015 13:40:20'

SELECT FORMAT(@t,'HH:mm') AS timefieldformatted

declare @dt datetime ='20151015 13:40:20'

SELECT FORMAT(@dt,'HH:mm')  AS datetimefieldformatted

declare @dt2 datetime2 ='20151015 13:40:20'

SELECT FORMAT(@dt2,'HH:mm')  AS datetime2fieldformatted

declare @dto datetimeoffset ='20151015 13:40:20'

SELECT FORMAT(@dto,'HH:mm')  AS datetimeoffsetfieldformatted

The output is as shown below

As you see from the above FORMAT was able to apply the passed specifiers over the value and apply the requested format in all cases except in the case of time datatype. So what is so special about time datatype which is causing this weird behavior from FORMAT function? Lets investigate

Reason

To understand the reason lets first refer the documentation of FORMAT function as laid out by MSDN

https://msdn.microsoft.com/en-us/library/hh213505.aspx

The below explanation is taken from the above link

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

..

If you see the above table you can see that time datatype in SQLServer  is mapped to TimeSpan .NET datatype

Now if you check TimeSpan datatype documentation for .NET 

https://msdn.microsoft.com/en-us/library/system.timespan(v=vs.110).aspx

You can see this

A TimeSpan object represents a time interval (duration of time or elapsed time) that is measured as a positive or negative number of days, hours, minutes, seconds, and fractions of a second. The TimeSpan structure can also be used to represent the time of day, but only if the time is unrelated to a particular date

Now if we check the standard format strings for the TimeSpan datatype it only supports c, g and G as the specifiers

https://msdn.microsoft.com/en-us/library/ee372286(v=vs.110).aspx

The way to specify custom specifiers is discussed in the below link

https://msdn.microsoft.com/en-us/library/ee372287(v=vs.110).aspx

So as per the above links we need to use query as below to get the required result

declare @t1 time ='20151015 13:40:20'

SELECT FORMAT(@t1,'c') AS timefieldformatted

declare @t2 time ='20151015 13:40:20'

SELECT FORMAT(@t2,'g') AS timefieldformatted

declare @t3 time ='20151015 13:40:20'

SELECT FORMAT(@t3,'hh\:mm') AS timefieldformatted

The output will be as below

Summary

As seen from the above illustrations we should use specifiers as c, g or hh:\mm for getting time datatype values in the formats as hh:mm as the implementation of FORMAT function maps time datatype to TimeSpan .NET datatype.

 

Thanks to Erland who gave the solution in the forum discussion and cleared up the doubts in all of us. Hopefully this blog will help to improve the clarity on the behavior of FORMAT function to a wider audience