Convert Time DataType into AM PM Format:

In SQL 2012 you can use the Format() function.

https://technet.microsoft.com/en-us/library/hh213505%28v=sql.110%29.aspx

Skip casting if the column type is (datetime).

Example:

SELECT FORMAT(StartTime,'hh:mm tt') AS StartTimeFROM TableA

Use following syntax to convert a time to AM PM format.

Replace the field name with the value in following query.

select CONVERT(varchar(15),CAST('17:30:00.0000000' AS TIME),100)

Here are the various ways you may pull this (depending on your needs).

Using the Time DataType:

DECLARE @Time Time = '15:04:46.217'SELECT --'3:04PM'       CONVERT(VarChar(7), @Time, 0),       --' 3:04PM' --Leading Space.       RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7),       --' 3:04 PM' --Space before AM/PM.       STUFF(RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' '),       --'03:04 PM' --Leading Zero.  This answers the question above.       STUFF(RIGHT('0' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' ')       --'03:04 PM' --This only works in SQL Server 2012 and above.  :)       ,FORMAT(CAST(@Time as DateTime), 'hh:mm tt')--Comment out for SS08 or less.

Using the DateTime DataType:

DECLARE @Date DateTime = '2016-03-17 15:04:46.217'SELECT --' 3:04PM' --No space before AM/PM.       RIGHT(CONVERT(VarChar(19), @Date, 0), 7),       --' 3:04 PM' --Space before AM/PM.       STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' '),       --'3:04 PM' --No Leading Space.       LTRIM(STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' ')),       --'03:04 PM' --Leading Zero.       STUFF(REPLACE(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), ' ', '0'), 6, 0, ' ')       --'03:04 PM' --This only works in SQL Server 2012 and above.  :)       ,FORMAT(@Date, 'hh:mm tt')--Comment line out for SS08 or less.