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.

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...
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...