How to Group by an HOUR - SQL query help needed

Posted on 2004-10-07
Last Modified: 2008-02-01
the question is probably not as easy as the subject looks like.
In MS Access I created a view, which I need to upsize to MS SQL (MSDE) now. But I used a way which is not supported in MSSQL.
Here it comes:

I have a table "logs" with fields - ID, log_date, issue
1     23.09.2004 11:50:52     aaa
2     23.09.2004 11:50:53     bbb
3     23.09.2004 12:10:25     ccc

now I have a view querry:

SELECT ID, Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00" AS New_Date, issue FROM logs;

which returns
1     23.09.2004 11:00:00     aaa
2     23.09.2004 11:00:00     bbb
3     23.09.2004 12:00:00     ccc

NOW, I need to do this in MSSQL, but there are two issues:
- first I do not have a HOUR function, which will return the hour from a date/time stamp
- second, I do not want to use this amateur construction, as I believe, there must be a btter way how to convert a date/time stamp to a new one, which gets rid of the minutes and seconds part of the original date/time stamp (or later also get rid of the hour as well and leaves only the date)

The reason, I need to do queries then, which are GROUPED BY the whole HOUR, i.e. I will then receive from above example such grouped recordset

IDcount      Groupeddatebyhour
2               23.09.2004 11:00:00
1               23.09.2004 12:00:00

Hope, I described it clearly.

Thanks for any help.

Question by:keson
  • 4
  • 3
  • 3
  • +1
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12247094
This should work, you where in fact very close:

SELECT count(*), Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00" AS New_Date, issue FROM logs
group by Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00"

LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12247114
hour functino is exists in sql....  but datepart is exists....

SELECT count(*), datepart(year, [log_date]) & "/" & datepart(month, ([log_date]) & "/" & datepart(day, [log_date]) & " " & datepart(hour, [log_date]) & ":00:00" AS New_Date, issue FROM logs
group by datepart(year, [log_date]) & "/" & datepart(month, ([log_date]) & "/" & datepart(day, [log_date]) & " " & datepart(hour, [log_date]) & ":00:00"

LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12247123
From sql server books online.....

Returns an integer representing the specified datepart of the specified date.

DATEPART ( datepart , date )


Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart                                   Abbreviations
year                                         yy, yyyy
quarter                                     qq, q
month                                       mm, m
dayofyear                                  dy, y
day                                           dd, d
week                                        wk, ww
weekday                                   dw
hour                                         hh
minute                                      mi, n
second                                     ss, s
millisecond                               ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.


Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date'

Here is the result set:

Current Date                
Feb 18 1998 11:46PM        

SELECT DATEPART(month, GETDATE()) AS 'Month Number'

Here is the result set:

Month Number

This example assumes the date May 29.


Here is the result set:


(1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.


Here is the result set:

----- ------ ------
1     1      1900

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 14

Accepted Solution

Thandava Vallepalli earned 40 total points
ID: 12247143

SELECT count(*), datepart(year, [log_date]) + "/" + datepart(month, ([log_date]) + "/" + datepart(day, [log_date]) + " " + datepart(hour, [log_date]) + ":00:00" AS New_Date, issue FROM logs
group by datepart(year, [log_date]) + "/" + datepart(month, ([log_date]) + "/" + datepart(day, [log_date]) + " " + datepart(hour, [log_date]) + ":00:00"

LVL 17

Assisted Solution

BillAn1 earned 35 total points
ID: 12247302
the most 'efficient' method is to use CONVERT function to do the formatting for you.
Since your example uses a non-standard format of  "yy/mm/dd HH:00:00" you will need to use 2 formats (11 - yy/mm/dd and 108 - HH:mm:ss) and concatenat them together, and truncate the 108 format to only 3 characters :
If you use the DATEPART functions etc, you still ahve to explicitly cast all the numbers to strings, and worry about inserting leading 0's etc

select count(*),
convert(char(8), log_date, 11) + ' ' + convert(char(3),log_date, 108) + '00:00'
from logs
group by convert(char(8), log_date, 11)+ ' ' + convert(char(3),log_date, 108) + '00:00'

however, if you just want the output in a standard datetime format, you can do it with one conversion :

select count(*),
convert(char(13),log_date, 20) + ':00:00'
from logs
group by convert(char(13),log_date, 20) + ':00:00'


Author Comment

ID: 12247597

thanks a lot. I knew it must be possible. I will have to study those options for convert as I see one can do much more with those.

If I can trouble you again, please, have a look at my next post, which is related to this issue and I want give away another 100 points for a sollution.
It is about SQL querry which will probably save me hours of try/error. Just wait till I sum it in a question.

Thanks again for your help.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12247663
seems this time i was too fast to get noticed / points :-)

PS: don't worry about points!!!

Author Comment

ID: 12247965
to angelIII,
sorry mate. You were fast enough and your answer was correct. Only I needed sollution for MS SQL server. The one I had now looks exactly the way you did it. But it does not work under the blooddy MS stuff. So I gave points to those who brought me a new stuff this time. Well, you will have a chance with the next question, I am still unable to form the way you will be able to understand it.


LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12248082
Indeed, sorry for that.
Ok, let me suggest this solution for literally removing minutes and seconds from your date/time

This would be the view query:
SELECT ID, dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) AS New_Date, issue FROM logs;

similary, the GROUP BY:
SELECT count(*), dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) AS New_Date, issue FROM logs
GROUP BY dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) ;

This will remove the necessity to do several conversions which for sure are less efficient than this one.

Anyhow, glad we could help

Author Comment

ID: 12248996
to: angelIII,

Great! I didn't even expect that such thing is possible. I can then indeed get rid of virtually any part of the date string for a specific "group by" needs! Right?

I have to say honestly, at the moment I do not understand what does the query you wrote above do, but I will try to understand it part by part.


My initial intention was to design an ASP code, which can run on both MS Access (in test env.) as well as on MS SQL database in live env. However, I am experiencing, that there are functions in both databases which are noth supported in the other one. Like the dateadd, I guess is not supported in MS Access.

I will have to probably do a global variable which will decide whether the database is MS Access or MS SQL and do two different queries for each one.

Or any other idea for Multidatabase code?

P.S. Can I give you just so few points?

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12249114
>>I can then indeed get rid of virtually any part of the date string for a specific "group by" needs! Right?

>>Or any other idea for Multidatabase code?
not really. Have tried doing this for oracle/sql server, I have ended up doing 2 implementations of a "STORAGE" class, each computes the SQL necessary as requested... Trying to stay compatible is senseless, as you defeat the optimization possiblities of the different products...

>>I have to say honestly, at the moment I do not understand what does the query you wrote above do, but I will try to understand it part by part.
No problem, just start off this query:
select getdate(), datepart(minute, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate())
The first column displays the current date/time, the second the actual minute value. The last just sets the minute value to 0, similar to what I suggested in the query for you.

>>P.S. Can I give you just so few points?
As said, don't matter the points. Getting ~20-50K points per month, so I don't mind them anyhow.


LVL 17

Expert Comment

ID: 12250302
be careful using this structure.
Subtracting the minutes / secods will NOT give you an exact hour, as there are still miliseconds stored in the datetime. When you display it,  e.g. select getdate(), datepart(minute, getdate()),
varchar(100),dateadd(ss, - datepart(ss, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate()))
it may look like it has been rounded correctly, but this is only the display not showing you the full milisecs correctly. if you were to use this in a group by you would get multiple records, not one.
to hilight this, use format 121 to display the full value including ms -
select getdate(), datepart(minute, getdate()),
convert(varchar(100),dateadd(ss, - datepart(ss, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate())),121)

again, the cleanest way to truncate to hour is to use the convert function : select convert(char(13),getdate(), 20) + ':00:00'

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 39
disk usage reporting tools 27 52
Want an individual results display div 8 43
SSRS Page Header from Group Data 2 24
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question