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

DECLARE @temp varchar(50)
set @temp = convert(varchar(max),STUFF(STUFF('645P',2,0,':'),5,0,' '))
select @temp

SELECT CASE
        WHEN RIGHT(@temp,1)='P' THEN REPLACE(@temp,'p','PM')
        WHEN RIGHT(@temp,1)='A' THEN REPLACE(@temp,'a','AM')
        END

Answer 2

    SELECT CONVERT(VARCHAR(20),GETDATE(),106)+' '+CONVERT(VARCHAR(20),CONVERT(TIME,GETDATE()),100)

-> RESULT : 30 Jan 2023 3:02PM

Answer 3

I wonder why the code below was not mentioned by anyone.

select  FORMAT(CAST(@Your_time_data_type AS datetime),'hh:mm tt') AS TimeStr

eg. select FORMAT(CAST('12:30' AS datetime),'hh:mm tt') AS TimeIn

Answer 4

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)

Output: 5:30PM

Better option is available with Sql 2012. First parameter should be of datetime data type.

DECLARE @d DATETIME = '10/01/2011 13:14';
SELECT FORMAT(@d,'hh:mm tt') 

Output : 01:14 PM

Answer 5

One option is to use the FORMAT() function like this:

-- Returns "03:15 AM"
DECLARE @iniTme TIME = '03:15:30'
SELECT FORMAT(CAST(@iniTme AS DATETIME),  'hh:mm tt')

-- Returns "03:15 PM"
DECLARE @endTme TIME = '15:15:30'
SELECT FORMAT(CAST(@endTme AS DATETIME),  'hh:mm tt')

Answer 6

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

almost works perfectly except for the space issue. if that were changed to:

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

...then you get the space. And additionally, if the column being converted is already of TIME format, you can skip the cast if you reference it directly.

Final answer:

select CONVERT(varchar(15),StartTime,22)

Answer 7

Using @Saikh's answer above, the 2nd option, you can add a space between the time itself and the AM or PM.

REVERSE(LEFT(REVERSE(CONVERT(VARCHAR(20),CONVERT(TIME,myDateTime),100)),2) + ' ' + SUBSTRING(REVERSE(CONVERT(VARCHAR(20),CONVERT(TIME,myDateTime),100)),3,20)) AS [Time],

Messy I know, but it's the solution I chose. Strange that the CONVERT() doesn't add that space automatically. SQL Server 2008 R2

Answer 8

> SELECT CONVERT(VARCHAR(30), GETDATE(), 100) as date_n_time
> SELECT CONVERT(VARCHAR(20),convert(time,GETDATE()),100) as req_time
> select convert(varchar(20),GETDATE(),103)+' '+convert(varchar(20),convert(time,getdate()),100)

> Result (1):- Jun  9 2018 11:36AM
> result(2):-  11:35AM
> Result (3):-  06/10/2018 11:22AM

Answer 9

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 10

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

Answer 11

Try this:

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

Answer 12

Try this:

select CONVERT(VARCHAR(5), ' 4:07PM', 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), ' 4:07PM', 9),2) as ConvertedTime

Answer 13

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.

Answer 14

This returns like 11:30 AM

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

Answer 15

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 16

SELECT CONVERT(varchar, StartTime, 100) AS ST,
       CONVERT(varchar, EndTime, 100) AS ET
FROM some_table

or

SELECT RIGHT('0'+ LTRIM(RIGHT(CONVERT(varchar, StartTime, 100),8)),8) AS ST,
       RIGHT('0'+ LTRIM(RIGHT(CONVERT(varchar, EndTime, 100),8)),8) AS ET
FROM some_table