如何在 SQL Server 中将 hh:mm:ss 转换为 hh:mm? [英] How do I convert hh:mm:ss to hh:mm in SQL Server?

问题描述

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

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

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

推荐答案

一般来说，时间戳集不是 有序，这意味着您无法获得最后"时间戳，其时间部分最多为分钟2009-05-06 14:58.

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.

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

In SQL Server, which keeps the time part of a datetime as a number of 1/300 second 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 TIMESTAMP storage methods.

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

That means you'll need to split your BETWEEN condition into two conditions, one of which being strict less than the next minute:

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

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

This solution will efficiently use indexes on Date

这篇关于如何在 SQL Server 中将 hh:mm:ss 转换为 hh:mm?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！