FORMAT Function Behavior Difference - Datetime vs Time Datatypes
Introduction
One of the really useful functions that got introduced in SQL 2012 was the FORMAT function.
Many a time when we want to do data exports to spreadsheets, files, etc. we can't rely upon front-end layer for the formatting logic. In such cases it is best to do the formatting in database code itself whilst doing the data export. FORMAT function provides a convenient way to convert numeric and datetime-based values to required formats by means of format specifiers.
This article discusses some of the differences in FORMAT behavior which we should be aware of while working with time and datetime-based values. This article is an adaptation from my original article posted here.
Scenario
To illustrate the difference in behavior, consider the small code snippet below
declare @dt datetime = '20171025 13:14:45'
SELECT FORMAT(@dt,'dd MMM yyyy hh:mm:ss tt')
The result is shown below:
The results match the expectation.
Repeating the same method for a time-based datatype gives the below result:
declare @tm time = '14:34:56'
SELECT FORMAT(@tm,'hh:mm:ss tt')
This is not what was expected from the code as the format specifiers used above were consistent with the earlier example. To understand the reason, we would need some analysis on the FORMAT function behavior.
FORMAT function - Background
Referring to FORMAT function documentation we can see that the interpretation of the format specifiers passed to the function is based on the .NET datatype that is mapped to the datatype of the value being passed i.e first argument of FORMAT function.
The mapping table for FORMAT function is given below
From the highlighted part we can see that the corresponding .NET datatypes mapped for DateTime vs time in SQLServer are different. DateTime is mapped to .NET DateTime datatype whereas time is mapped to TimeSpan datatype in .NET.
If the supported format specifiers are analyzed between DateTime and TimeSpan datatypes in .NET we can notice the below differences:
| DateTime | TimeSpan |
| Both HH/H and hh/h can be used for getting the hour part with upper case giving hour in 24 hr format and lower case giving hour in 12 hr format. | Only hh is supported and it always gives time in 24 hr format |
| : is the time separator | Does not support any specifiers like : so any custom specifiers have to be escaped using \ character |
| t or tt can be used to get AM/PM designator | Designator not supported as the value returned is always in 24 hr format |
Taking all the above points into consideration we need to tweak code like below for getting the time value formatted
However, if we need the time-based value in format like 02:34:56 PM then its not possible directly inside FORMAT function. The only workaround in that case is to first cast or convert the value to DateTime and then pass it as an argument inside FORMAT
The illustration is given below:
declare @tm time = '14:34:56'
SELECT FORMAT(CAST(@tm as datetime),'hh:mm:ss tt')
Similarly as per the table above, for returning the individual parts from a time datatype we need to use corresponding format specifiers. For example, in the case of getting the hour part, the difference in the format specifier can be seen below
declare @dt datetime = '20171025 02:34:56 PM',@tm time = '02:34:56 PM'
SELECT FORMAT(@dt,'HH') AS HourOFDate,FORMAT(@tm,'hh') AS HourOfTime
Conclusion
As evident from the illustrations above, the behavior of FORMAT function is different while dealing with time-based datatypes compared to that of date, datetime, and datetime2 datatypes. This difference is attributed to the difference in .Net datatype to which time is mapped.