CODEWITHSUNDEEP
sqlsql-servert-sql
Dryden Long
Dryden Long

Reputation: 10182

T-SQL Get Rows Per Hour Regardless of Day

Using the accepted answer found in this post Count rows per hour in SQL Server with full date-time value as result I am able to get a count of records by the hour, however, I would like to make it so that the day does not matter.

How can I get a count of records by the hour of the day, regardless of what day the record is from?

For example, the following table:

+----------+-------------------------+
| instance |        datetime         |
+----------+-------------------------+
| A        | 2017-01-10 11:01:05.267 |
| B        | 2017-01-13 11:07:05.783 |
| C        | 2017-01-14 11:37:05.593 |
| D        | 2017-01-17 11:37:38.610 |
| E        | 2017-01-17 11:47:04.877 |
| F        | 2017-01-15 12:14:34.127 |
| G        | 2017-01-17 12:15:09.373 |
| H        | 2017-01-09 13:58:06.013 |
+----------+-------------------------+

Would result in the following dataset:

+-------------+-------------+
| recordCount |  timeStamp  |
+-------------+-------------+
|           5 | 11:00:00.00 |
|           2 | 12:00:00.00 |
|           1 | 13:00:00.00 |
+-------------+-------------+

Upvotes: 2

Views: 895

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

If you are using SQL SERVER 2012 or above then use FORMAT

SELECT Count(1) AS Recordcount,
       Format([datetime], 'HH:00:00')
FROM   yourtable
GROUP  BY Format([datetime], 'HH:00:00')

or CONCAT and DATEPART

SELECT Concat(Datepart(HOUR, [datetime]), ':00:00'),
       Count(*)
FROM   Yourtable
GROUP  BY Datepart(HOUR, [datetime])

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use DATEPART:

SELECT DATEPART(HOUR, [datetime]), COUNT(*)
FROM mytable
GROUP BY DATEPART(HOUR, [datetime])

The function returns an integer, essentially extracting the hour value from the DATETIME field.

Upvotes: 6

Related Questions