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.

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...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...