Formatting time variable in SQL Server

A colleague of mine recently had an issue when trying to format a variable of the time data type. He was trying to return the time in the HH:mm format and was using the following code:

DECLARE @Time TIME = '13:05'
DECLARE @DateTime DATETIME = '20170413 13:05'

SELECT 'Time' AS Variable, @Time as Unformatted, FORMAT(@Time,'HH:mm') AS Formatted

To our mutual surprise, the result was the following:

We tried to do the same thing with a date time, and that worked the way we expected it to work… correctly.

So, why isn’t it working with the time data type? Some Google-fu later, we had found a plethora of pages claiming this is normal. Fortunately, there was a workaround, which is to cast the variable to a date time before applying the formatting. Although we tested this method and found it to be valid, it isn’t what I would call a clean solution. Luckily, hidden within books online, we found a remark that explains the issue.

“FORMAT relies upon CLR formatting rules which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.“

To me, this still doesn’t explain why the issue only affects the time data type. It does, however, give us a clean solution. So let’s give it a try.

Hmmm, it seems we are stilling missing a part of the solution. Digging in my memory,I recalled that the time data type is based on a 24-hour system. When working with a date time, we use “HH” to indicate the time in 24-hour system and “hh” for the 12-hour system. Lo and behold, we’ve found the solution to our problem!

As the time data type has only one clock system (24h), I understand that there is no need to support both “hh” and “HH”. However, wouldn’t it have been more logical to use “HH” instead of “hh”? Or even better, to accept both “HH” and “hh” as input. Anyway, we now have a clean solution to our little problem.