如何从“HH:mm tt”格式的SQL Server 2005 datetime中获取时间部分

How to get time part from SQL Server 2005 datetime in HH:mm tt format

如何从HH:mm tt格式的SQL Server 2005 datetime中获取时间部分

E.g.

如。

11:25 AM
14:36 PM

9 个解决方案

#1

One way is:

一种方法是:

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

If you have a look at Books Online here, format 100 is the one that has the time element in the format you want it in, it's just a case of stripping off the date from the front.

如果你在网上看一下书，格式100有你想要的格式的时间元素，它只是一个从前面去掉日期的例子。

#2

You'll need two converts, one to get the HH:mm time, and one to get AM/PM. For example:

你需要两个皈依者，一个获得HH:mm时间，一个获得AM/PM。例如:

declare @date datetime
set @date = '20:01'
SELECT CONVERT(VARCHAR(5), @date, 108) + ' ' +
       SUBSTRING(CONVERT(VARCHAR(19), @date, 100),18,2)

This prints:

这个打印:

20:01 PM

In a select query, replace @date with your column's name.

在select查询中，将@date替换为您的列的名称。

#3

SQL Server 2008

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Result

结果

HourMinuteSecond: 13:06:56.5770000
DateOnly:         2012-07-26

SQL Server 2000/2005

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/

http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/

#4

SQL Server 2008

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Result:

结果:

HourMinuteSecond: 13:06:56.5770000
DateOnly:         2012-07-26

SQL Server 2000/2005

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

#5

select substring(CONVERT(VARCHAR, getdate(), 114),1,5)

resault : 22:05

resault:球队的

#6

You need to use CONVERT function:

你需要使用转换函数:

CONVERT(VARCHAR, yourdatetimefiled, 114) AS [HH:MI(12H)]

#7

select cast(getdate() as time)) [time],

#8

This gives you an actual datetime and not varchar

这将提供一个实际的datetime，而不是varchar

CAST(LEFT(YOURDATETIME,12) AS SMALLDATETIME) AS YOURNEWDATE

#9

select right(convert(char(20),getdate(),0),7)

No check though

没有检查尽管