SQL Server 按小时对时间戳进行分组，但保留为日期格式(不想拉出小时)

Question

当我想按天、按对一堆时间戳进行分组时

CONVERT (datetime, CONVERT (varchar, dbo.MEASUREMENT_Battery.STAMP, 101)) 

它为我生成了一个"日期"戳，SQL Server仍然将其视为日期，并且可以按日期进行排序和使用。

我想弄清楚的是，是否有可能按小时做同样的事情。我试过

CAST(DATEPART(Month, STAMP) AS varchar) + '/' + CAST(DATEPART(Day, STAMP) AS varchar) + '/' + CAST(DATEPART(Year, STAMP) AS varchar) + ' ' + CAST(DATEPART(Hour, STAMP) AS varchar) + ':00:00.000'

这"有效"，但SQL Server不再将其视为日期，因此我无法正确排序。

不过，我想要的最终结果是正确的：例如：9/9/2015 9:00:00.000

Answer 1

在绝对必须"呈现"结果之前，不要转换为字符串。

CONVERT（）或FORMAT（）返回时间信息的字符串表示

下面的方法返回一个截断为小时的日期时间值，而不需要进行字符串操作（因此速度很快）。

select
    dateadd(hour, datediff(hour,0, dbo.MEASUREMENT_Battery.STAMP ), 0)
  , count(*)
from dbo.MEASUREMENT_Battery
group by
    dateadd(hour, datediff(hour,0, dbo.MEASUREMENT_Battery.STAMP ), 0)

SQL Fiddle

MS SQL Server 2014架构设置:

CREATE TABLE MEASUREMENT_Battery
    ([STAMP] datetime)
;
INSERT INTO MEASUREMENT_Battery
    ([STAMP])
VALUES
    ('2015-11-12 07:40:15'),
    ('2015-11-12 08:40:15'),
    ('2015-11-12 09:40:15'),
    ('2015-11-12 10:40:15'),
    ('2015-11-12 11:40:15'),
    ('2015-11-12 12:40:15'),
    ('2015-11-12 13:40:15'),
    ('2015-11-12 14:40:15')
;

注：下面[Stamp]列的输出是默认显示

结果：

|                            |   |
|----------------------------|---|
| November, 12 2015 07:00:00 | 1 |
| November, 12 2015 08:00:00 | 1 |
| November, 12 2015 09:00:00 | 1 |
| November, 12 2015 10:00:00 | 1 |
| November, 12 2015 11:00:00 | 1 |
| November, 12 2015 12:00:00 | 1 |
| November, 12 2015 13:00:00 | 1 |
| November, 12 2015 14:00:00 | 1 |

如果你绝对坚持以相同的方式支付日期/时间值，那么你可以在select子句中添加显示格式（但在逐子句分组中不需要！）

select
    FORMAT(dateadd(hour, datediff(hour,0, dbo.MEASUREMENT_Battery.STAMP ), 0) , 'MM/dd/yyyy HH')
  , count(*)
from dbo.MEASUREMENT_Battery
group by
    dateadd(hour, datediff(hour,0, dbo.MEASUREMENT_Battery.STAMP ), 0)

Answer 2

当您使用DateTime样式101时（在第二个CONVERT结束时），日期将转换为mm/dd/yyyy，时间将转换为00:00:00.000，如下所述：https://msdn.microsoft.com/en-us/library/ms187928.aspx

现在，根据我从你的问题中了解到的情况，你也希望包括时间，这可以这样做：

SELECT FORMAT(STAMP , 'MM/dd/yyyy HH') + ':00:00.000'

注意：

• "：00:00.000"是可选的，只是为了获得更好的输出
• 这仅适用于SQL Server 2012及更高版本

通过一些测试日期的测试，我们将看到我们得到预期的结果：

-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #T
-- Create temp table
CREATE TABLE #T ( myDate DATETIME )
-- Insert dummy values
INSERT INTO #T VALUES ( '2015-12-25 14:00:00.000' ) -- 14
INSERT INTO #T VALUES ( '2015-12-25 14:00:00.000' ) -- 14
INSERT INTO #T VALUES ( '2015-12-25 15:00:00.000' )
INSERT INTO #T VALUES ( '2015-12-25 16:00:00.000' )
INSERT INTO #T VALUES ( '2015-12-25 17:00:00.000' ) -- 17
INSERT INTO #T VALUES ( '2015-12-25 17:00:00.000' ) -- 17
-- Select query
SELECT COUNT( myDate ), MAX( FORMAT( myDate , 'MM/dd/yyyy HH') + ':00:00.000' ) FROM #T
GROUP BY DATEPART( hour, myDate )

输出：

2   12/25/2015 14:00:00.000
1   12/25/2015 15:00:00.000
1   12/25/2015 16:00:00.000
2   12/25/2015 17:00:00.000