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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature
If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before....
Creating maps with R and Power BI
The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you are creative and have knowledge about the...
Reading notifications
Sending monitoring alerts through Telegram
What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the...
Send mails with Azure Elastic Database Jobs
The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want similar functionality in Azure SQL...
Sorting matrices in Power BI
Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour...
The world of data is evolving
The data landscape has changed dramatically over recent years, the world of data is evolving. In the past, we mainly heard that we needed to...