在SQL中按小时分组DateTime

I have a table in my database which contains a column called DateTimes. This column has DateTime data inside it which I would like to select the different hours for which this table has data for. For example, if the column has these entries:

我的数据库中有一个表，其中包含一个名为DateTimes的列。这个列里面有DateTime数据，我想选择这个表有数据的不同小时数。例如，如果列包含以下条目：

2015-05-03 01:06:45
2015-05-03 04:51:09
2015-05-03 05:08:11
2015-05-03 09:33:35
2015-05-03 13:46:38

I would like to return

我想回来

2015-05-03 01:00:00
2015-05-03 04:00:00
2015-05-03 05:00:00
2015-05-03 09:00:00
2015-05-03 13:00:00

I have tried the following which is returning an error:

我尝试了以下返回错误：

SELECT DateTimes
FROM MyTable
GROUP BY DATEPART(hh, DateTimes)

I feel like this should be easy to do but I can't seem to get it right (I'm very new to SQL). I'm using MS SQL Management Studio 2012 to access my database.

我觉得这应该很容易，但我似乎无法做到正确（我对SQL很新）。我正在使用MS SQL Management Studio 2012来访问我的数据库。

4 个解决方案

#1

Try this.

尝试这个。

Test Data:

测试数据：

DECLARE @MyTable AS TABLE(DateTimes DATETIME)
INSERT INTO @MyTable(DateTimes)
VALUES('2015-05-03 01:06:45')
,('2015-05-03 04:51:09')
,('2015-05-03 05:08:11')
,('2015-05-03 09:33:35')
,('2015-05-03 13:46:38')

Query:

查询：

  SELECT Hourly
    FROM (SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTimes), 0) AS Hourly
            FROM @MyTable) AS DatesAsHours
GROUP BY Hourly

Results:

结果：

Hourly
2015-05-03 01:00:00.000
2015-05-03 04:00:00.000
2015-05-03 05:00:00.000
2015-05-03 09:00:00.000
2015-05-03 13:00:00.000

#2

What you really want is to Format your date , so it hides minutes/seconds,

你真正想要的是格式化你的日期，所以它隐藏分钟/秒，

SELECT CONVERT(VARCHAR(25), DateTimes, 100) FROM MyTable

#3

Try the following:

请尝试以下方法：

SELECT DateTimes
FROM MyTable
GROUP BY DATEPART(hh, DateTimes), DateTimes;

You were simply missing the DateTimes field directly in your group by.

您只是直接在组中错过了DateTimes字段。

Also, from the rest of your question, you may want to do the following:

此外，从您的其余问题来看，您可能希望执行以下操作：

SELECT FORMAT(DateTimes, 'dd-MM-yyyy hh:00:00', 'en-US')
FROM MyTable
GROUP BY DATEPART(hh, DateTimes), FORMAT(DateTimes, 'dd-MM-yyyy hh:00:00', 'en-US');

#4

You need to specify the formatting in the select. Doing DATEPART in Group By will cause rows with the same hour to be in the same group. Also, DATEPART only returns the hour part of the datetime.

您需要在select中指定格式。在Group By中执行DATEPART将导致具有相同小时的行位于同一组中。此外，DATEPART仅返回日期时间的小时部分。

You can try using DATE_FORMAT function, like:

您可以尝试使用DATE_FORMAT函数，例如：

select DATE_FORMAT(DateTimes, "%Y-%m-%d %H:00:00") from MyTable

See man page for DATE_FORMAT for available formatting options.

有关可用的格式选项，请参阅DATE_FORMAT的手册页。

#1