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.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...