SQL Tips: Demystifying FORMAT behavior in Datetime vs Time Values

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

https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

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

TimeSpan Accepted Format Specifiers

On contrast, the acceptable specifiers for DateTime are the below

DateTime Accepted Format Specifiers

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.