将时间数据类型转换为AM PM格式:

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

我有一个表，它有两个字段，如“StartTime”和“EndTime”。这两列的数据类型是时间。

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?

当我选择表格时。如何以AM PM格式获得时间?

11 个解决方案

#1

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

使用以下语法将时间转换为AM PM格式。

Replace the field name with the value in following query.

用以下查询中的值替换字段名。

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

#2

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

在SQL 2012中，可以使用Format()函数。

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

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

Skip casting if the column type is (datetime).

如果列类型是(datetime)，则跳过强制转换。

Example:

例子:

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

#3

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:

使用DateTime数据类型:

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.

#4

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

#5

select case 
      when DATEPART(hour,'17:30:00.0000000') > 12 
      then cast((DATEPART(hour,'17:30:00.0000000') - 12) as varCHAR(2)) + ':' + cast(DATEPART(minute,'17:30:00.0000000') as varCHAR(2)) + ' PM'
      else cast(DATEPART(hour,'17:30:00.0000000') as varCHAR(2)) + ':' + cast(DATEPART(minute,'17:30:00.0000000') as varCHAR(2)) + ' PM'
   end

#6

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

多个函数，但这将为您提供所需(在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];

选择SUBSTRING(CONVERT(varchar(20)，StartTime(22)， 10,11)作为Start, SUBSTRING(CONVERT(varchar(20)，EndTime(22)， 10,11)作为结束FROM [TableA];

#7

This returns like 11:30 AM

大约上午11:30回来

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

#8

Try this:

试试这个:

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

#9

Try this:

试试这个:

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

#10

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

#11

SELECT CONVERT(VARCHAR(30), GETDATE(), 100) as date_n_time SELECT CONVERT(VARCHAR(20),convert(time,GETDATE()),100) as req_time

选择CONVERT(VARCHAR(30)， GETDATE()， 100)作为date_n_time选择CONVERT(VARCHAR(20)， CONVERT(time,GETDATE()，100)作为req_time

Result (1):- Jun 9 2018 11:36AM result(2):- 11:35AM

结果(1):- 2018年6月9日11:36AM结果(2):- 11:35AM