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
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
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
The way to specify custom specifiers is discussed in the below link
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
