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?
This question is related to
sql-server
sql-server-2008
select
time
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
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.
Try this:
select CONVERT(varchar(15),CAST('2014-05-28 16:07:54.647' AS TIME),100) as CreatedTime
> 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
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)
Try this:
select CONVERT(VARCHAR(5), ' 4:07PM', 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), ' 4:07PM', 9),2) as ConvertedTime
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];
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
This returns like 11:30 AM
select CONVERT(VARCHAR(5), FromTime, 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), FromTime, 9),2)
from tablename
select right(convert(varchar(20),getdate(),100),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
Source: Stackoverflow.com