如何在 SQL Server 中将 hh:mm:ss 转换为 hh:mm？

How do I convert hh:mm:ss to hh:mm in SQL Server?

提问by Penguen

How do I convert hh:mm:ss to hh:mm in SQL Server?

如何在 SQL Server 中将 hh:mm:ss 转换为 hh:mm？

select Count(Page) as VisitingCount,Page,CONVERT(VARCHAR(8),Date, 108) from scr_SecuristLog   
where Date between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by Page,Date order by [VisitingCount] asc

采纳答案by Quassnoi

In general, the set of timestamps is not well-ordered, this means you cannot get a "last" timestamp whose time part up to minutes is 2009-05-06 14:58.

通常，时间戳集不是有序的，这意味着您无法获得“最后”时间戳，其时间部分最多为 分钟2009-05-06 14:58。

In SQL Server, which keeps the time part of a datetime as a number of 1/300second fractions after midnight, this "last" timestamp would be 2009-05-06 14:58:59.997, but this is not guaranteed to be compatible with future releases of with other TIMESTAMPstorage methods.

在 中SQL Server，它将日期时间的时间部分保留为1/300午夜之后的第二个小数部分，这个“最后”时间戳将是2009-05-06 14:58:59.997，但这不能保证与其他TIMESTAMP存储方法的未来版本兼容。

That means you'll need to split your BETWEENcondition into two conditions, one of which being strict lessthan the next minute:

这意味着您需要将您的BETWEEN条件拆分为两个条件，其中一个是strict less下一分钟：

select Count(Page) as VisitingCount,Page,CONVERT(VARCHAR(8),Date, 108) from scr_SecuristLog   
where Date >= '2009-05-04 00:00:00'
      AND Date < DATEADD(minute, 1, '2009-05-06 14:58')
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by Page,Date order by [VisitingCount] asc

This solution will efficiently use indexes on Date

此解决方案将有效地使用索引 Date

回答by

CONVERT(VARCHAR(5),Date, 108)-- Gets only HH:mm

CONVERT(VARCHAR(5),Date, 108)-- 只获取 HH:mm

回答by Keith

SELECT Convert(varchar(5), GetDate(), 108)

Using varchar(5) will automatically truncate the date to remove the seconds.

使用 varchar(5) 将自动截断日期以删除秒数。

回答by u07ch

I dont think there is a built in function; usually do something like this

我认为没有内置功能；通常做这样的事情

SET @time = '07:45'
SET @date = CONVERT(DATETIME,@time)
SELECT @date

SELECT LEFT(CONVERT(VARCHAR,@date,108),5) 

回答by Mehrdad Afshari

For this specific need you should use the between method as noted by Quassnoi's answer. However, the general problem can be solved with:

对于此特定需求，您应该使用Quassnoi's answer所述的 between 方法。但是，一般问题可以通过以下方式解决：

select dateadd(second, -datepart(second, @date), @date)

回答by Jona

One way would be to use the RIGHT() function to crop the Date. Something like:

一种方法是使用 RIGHT() 函数来裁剪日期。就像是：

RIGHT(CONVERT(VARCHAR(8),Date, 108),5)

This will only work if number of characters is constant e.g. there is a leading zero if applicable. (Sorry havn't got SQL server here to test).

这仅在字符数不变的情况下才有效，例如，如果适用，有一个前导零。（抱歉，这里没有 SQL 服务器进行测试）。

A better way is to use the T-SQL datepart function to split and then re-concatinate the date parts so:

更好的方法是使用 T-SQL datepart 函数拆分然后重新连接日期部分，以便：

DARTPART("hh", CONVERT(VARCHAR(8),Date, 108))+":"+DARTPART("mi", CONVERT(VARCHAR(8),Date, 108))

References:

参考：

http://msdn.microsoft.com/en-us/library/ms174420.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx
http://msdn.microsoft.com/en-us/library/ms177532.aspx

http://msdn.microsoft.com/en-us/library/ms174420.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx
http://msdn.microsoft.com/en-我们/图书馆/ms177532.aspx

回答by Satyaprakash Samantaray

To get hh:mm format from today's date:

要从今天的日期获取 hh:mm 格式：

select getdate()

select convert(varchar(5),getdate(),108)

To get hh:mm format from any datetime column in a table data:

要从表数据中的任何日期时间列获取 hh:mm 格式：

select date_time_column_name from table_name where column_name = 'any column data name'

select convert(varchar(5),date_time_column_name,108) from table_name
where column_name = 'any column data name'

select creationdate from employee where Name = 'Satya'

select convert(varchar(5),creationdate,108) from employee 
where Name = 'Satya'

回答by Alex Purice

SELECT CAST(CAST(GETDATE() AS Time(0)) AS VARCHAR(5))