Convert Time DataType into AM PM Format:

Question

I have one Table which has two fields such as "StartTime" and "EndTime". The DataType of the Two columns are Time.

So the Values of the Table looks like as follows:

TableA:

            StartTime                EndTime
       ------------------         ----------------
        17:30:00.0000000          17:57:00.0000000

But I need the result as

            StartTime                EndTime
       ------------------         ----------------
            05:30 PM                 05:57 PM

When I select the table. How to get time in AM PM Format?

Answer 1

    select right(convert(varchar(20),getdate(),100),7)

Answer 2

Try this:

select CONVERT(varchar(15),CAST('2014-05-28 16:07:54.647' AS TIME),100) as CreatedTime

Answer 3

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 StartTime
FROM TableA

Answer 4

This returns like 11:30 AM

select CONVERT(VARCHAR(5), FromTime, 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), FromTime, 9),2)
from tablename

Answer 5

Multiple functions, but this will give you what you need (tested on SQL Server 2008)

Edit: The following works not only for a time type, but for a datetime as well.

SELECT SUBSTRING(CONVERT(varchar(20),StartTime,22), 10, 11) AS Start, SUBSTRING(CONVERT(varchar(20),EndTime,22), 10, 11) AS End FROM [TableA];

Answer 6

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)