CODEWITHSUNDEEP
sqlsql-server
Novastorm
Novastorm

Reputation: 1458

Grouping DateTime by hour in SQL

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:

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.

Upvotes: 2

Views: 7641

Answers (4)

gmiley
gmiley

Reputation: 6604

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.

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');

Upvotes: 2

Mack
Mack

Reputation: 2552

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

Upvotes: 1

DBug
DBug

Reputation: 2566

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.

You can try using DATE_FORMAT function, like:

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

See man page for DATE_FORMAT for available formatting options.

Upvotes: 1

ogres
ogres

Reputation: 3690

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

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

Upvotes: 1

Related Questions