In SQL Server, you can use the T-SQL FORMAT() function to format a time data type. However, if you need to add the AM/PM designator, you’ll need to use a different data type. This is because the time data type is specifically based on a 24 hour clock, and therefore the time is formatted as such.

Example 1 – Comparing ‘time’ with ‘datetime’

Here’s an example to demonstrate what happens if you try to format the ‘time’ data type with the AM/PM designator:

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

Result:

+------------+--------+
| datetime   | time   |
|------------+--------|
| 11:28 AM   | NULL   |
+------------+--------+

If you try to add the AM/PM designator to a ‘time’ value, you’ll get NULL.

Therefore, if you need to add AM or PM to a time data type, you’ll need to convert it to another data type first, and then format it.

Note that the FORMAT() function actually returns the result as a string anyway (unless the result is NULL).

If you’re wondering why there’s a backslash in the second format string, this is only required for the time data type, and it is used to escape the colon (and any periods). More about that here.

Example 2 – Converting ‘time’ to ‘datetime’

This example is almost identical to the previous example, except I try to make this more realistic. In this one, I explicitly set a variable as a ‘time’ data type and then try to format that. I then cast it as ‘datetime’ before formatting it again.

DECLARE @thetime time = '11:28:15'
SELECT 
  FORMAT(@thetime, 'hh\:mm tt') 'time',
  FORMAT(CAST(@thetime AS datetime), 'hh:mm tt') 'datetime';

Result:

+--------+------------+
| time   | datetime   |
|--------+------------|
| NULL   | 11:28 AM   |
+--------+------------+

If you prefer to use the CONVERT() function, here’s what that would look like:

DECLARE @thetime time = '11:28:15'
SELECT 
  FORMAT(@thetime, 'hh\:mm tt') 'time',
  FORMAT(CONVERT(datetime, @thetime), 'hh:mm tt') 'datetime';

Result:

+--------+------------+
| time   | datetime   |
|--------+------------|
| NULL   | 11:28 AM   |
+--------+------------+

Or you could just reassign the value to another variable of the desired type:

DECLARE @thetime time = '11:28:15'
DECLARE @thedatetime datetime = @thetime
SELECT 
  FORMAT(@thetime, 'hh\:mm tt') 'time',
  FORMAT(@thedatetime, 'hh:mm tt') 'datetime';

Result:

+--------+------------+
| time   | datetime   |
|--------+------------|
| NULL   | 11:28 AM   |
+--------+------------+

Example 3 – Single Letter AM/PM Designator

You can also use a single t to specify a single letter AM/PM designator:

SELECT 
  FORMAT(CAST('11:28:15' AS datetime), 'hh:mm t') 'AM',
  FORMAT(CAST('23:28:15' AS datetime), 'hh:mm t') 'PM';

Result:

+---------+---------+
| AM      | PM      |
|---------+---------|
| 11:28 A | 11:28 P |
+---------+---------+

Example 4 – Without using the FORMAT() Function

The FORMAT() function was introduced in SQL Server 2012. If you use an earlier version of SQL Server, you’ll need to use a different method to add the AM/PM designator. Here’s one way you could do that:

DECLARE @thetime time
SET @thetime = '11:28:15'
SELECT CONVERT(varchar(8), @thetime, 100) Result;

Result:

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

Alternatively, you can use replace 100 with 0 for the same result:

DECLARE @thetime time
SET @thetime = '11:28:15'
SELECT CONVERT(varchar(8), @thetime, 0) Result;

Result:

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