In SQL Server, when you use the T-SQL FORMAT() function to format a time data type, you need to remember to escape any colons or periods in your format string.

This is because the FORMAT() function 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.

Example 1 – Escaping a Colon

Here’s a basic example of using the FORMAT() function to format a time data type.

SELECT FORMAT(CAST('11:28:15' AS time), 'hh\:mm') Result;

Result:

+----------+
| Result   |
|----------|
| 11:28    |
+----------+

Note the backslash in the format string.

Example 2 – Escaping a Period

The same thing applies if we want to format it with a period:

SELECT FORMAT(CAST('11:28:15' AS time), 'hh\.mm') Result;

Result:

+----------+
| Result   |
|----------|
| 11.28    |
+----------+

Example 3 – Unescaped

Here’s what happens if we don’t escape the colon or period.

SELECT 
  FORMAT(CAST('11:28:15' AS time), 'hh:mm') 'Unescaped Colon',
  FORMAT(CAST('11:28:15' AS time), 'hh.mm') 'Unescaped Period';

Result:

+-------------------+--------------------+
| Unescaped Colon   | Unescaped Period   |
|-------------------+--------------------|
| NULL              | NULL               |
+-------------------+--------------------+

We get NULL in both cases.

Example 4 – Datetime (no need to escape)

You only need to escape the colon and period if the input value is of data type time. If it’s datetime (or datetime2 etc), you don’t need to escape them.

If I use the previous example, but switch the input values to datetime2, we get the desired result without needing to escape anything:

SELECT 
  FORMAT(CAST('11:28:15' AS datetime2), 'hh:mm') 'Unescaped Colon',
  FORMAT(CAST('11:28:15' AS datetime2), 'hh.mm') 'Unescaped Period';

Result:

+-------------------+--------------------+
| Unescaped Colon   | Unescaped Period   |
|-------------------+--------------------|
| 11:28             | 11.28              |
+-------------------+--------------------+

The same applies to sysdatetime:

SELECT 
  FORMAT(SYSDATETIME(), 'hh:mm') 'Unescaped Colon',
  FORMAT(SYSDATETIME(), 'hh.mm') 'Unescaped Period';

Result:

+-------------------+--------------------+
| Unescaped Colon   | Unescaped Period   |
|-------------------+--------------------|
| 04:46             | 04.46              |
+-------------------+--------------------+