Working with Time Values in SQL Server

Given that you have a column that is a datetime or smalldatetime datatype and this your data (by the way, in Management Studio, I entered the time w/o the date specified it defaulted to "today"):
select [testtime] from TableName
Output:
2010-10-14 06:00:00
2010-10-14 08:00:00
2010-10-14 23:00:00

To display only the time portion, using a 12 hour clock use this sql:
SELECT substring(convert(varchar(20), [testtime], 9), 13, 5) --time
+ ' '
+ substring(convert(varchar(30), [testtime], 9), 25, 2) --am/pm
FROM TableName
Output:
6:00 AM
8:00 AM
11:00 PM