Introduction
Since SQL 2012 , one of the functions which I used quite regularly on my day to day coding has been the FORMAT function.
The major use case has been in formatting date and time based values to custom date and time formats. Though I rely upon front end to do the formatting on most occasions, sometimes we’re required to do it at database layer as some tasks would be data feeds like Excel exports where formatted data has to be exported to Excel sheets.
This article clarifies the difference in the behavior of FORMAT function that you shall notice while working with datetime vs time based values
Scenario
Now lets set the scene for the topic on hand
Consider this small code snippet where I’m trying to format a standard datetime value to the format dd MMM yyyy hh:mm:ss AM/PM.
declare @dt datetime = ‘20171025 13:14:45’
SELECT FORMAT(@dt,’dd MMM yyyy hh:mm:ss tt’)
The result is shown below
All is as expected and we are good
Now lets try using FORMAT function on a time value
declare @tm time = ’14:34:56′
SELECT FORMAT(@tm,’hh:mm:ss tt’)
And the result is as below
This was something totally unexpected. The expectation was to get the time based value like 02:34:56 PM
Explanation
I decided to do some analysis on FORMAT function to get to the bottom of this problem.
The official documentation of FORMAT function also clearly states supporting date, datetime, datetime2, time datatypes as seen from here
But the catch is in the mapping table
If you check the documentation, time datatype alone is mapped to .Net datatype of TimeSpan while date, datetime, datetime2 are all mapped to DateTime datatype in .Net
The TimeSpan datatype accept custom specifiers as aid out by the article below
On contrast, the acceptable specifiers for DateTime are the below
Reading through the link you can note the below points regarding Format Specifiers
- TimeSpan doesnt accept : as a valid separator whereas DateTime accepts : as a valid time separator
- TimeSpan requires using as escape character to escape the : seperators
- TimeSpan accepts only h or hh as specifier for hour part always returned in 24hr clock whereas DateTime accepts both lower and upper case (h,hh,H,HH) with lowercase giving time in 12h and uppercase in 24hr clock
- DateTime accepts t or tt to return first or full part of AM/PM designator but TimeSpan doesnt support this as it always returns time in 24hr clock
Based on the above comparison we can see that for getting time value in hh:mm:ss format we need to use FORMAT function like this
declare @tm time = ’02:34:56′
SELECT FORMAT(@tm,’hh:mm:ss’)
And result is as shown
If you want to get format as 02:34:56 PM you need to cast it to datetime first before applying FORMAT function as per the stated points above
i.e like
declare @tm1 time = ’02:34:56′
SELECT FORMAT(CAST(@tm1 as datetime),’hh:mm:ss tt’)
And you shall get the intended result as shown
Conclusion
As seen from the illustration above, FORMAT function handles the datetime and time fields differently due to the difference in mapped .Net datatypes.
