How to Group by an HOUR - SQL query help needed
Posted on 2004-10-07
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;
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
2 23.09.2004 11:00:00
1 23.09.2004 12:00:00
Hope, I described it clearly.
Thanks for any help.