Go Premium for a chance to win a PS4. Enter to Win


How to Group by an HOUR - SQL query help needed

Posted on 2004-10-07
Medium Priority
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:Pedro 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 14

Accepted Solution

Thandava Vallepalli earned 160 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 140 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

by:Pedro Keson
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

by:Pedro Keson
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

by:Pedro Keson
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

824 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