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 | +-------------------+--------------------+