I like see if there is a way to get the same result as this script below with a one liner or just a lot less code. The script below works but I think there is a lot of over head here and the table is rather large.
(EDIT: The code below truncates DATETIME to the hour)
SELECT Convert(varchar,DATEPART(year, getdate()))
+'-'+
Case
When Convert(varchar,DATEPART(month, getdate()))<=9 Then '0'+Convert(varchar,DATEPART(month, getdate()))
Else Convert(varchar,DATEPART(month, getdate()))
End
+'-'+
Case
When Convert(varchar,DATEPART(day, getdate()))<=9 Then '0'+Convert(varchar,DATEPART(day, getdate()))
Else Convert(varchar,DATEPART(day, getdate()))
End
+' '+Convert(Varchar,DatePart(Hour,Getdate()))
+':00:00.00'
1条答案
按热度按时间fhg3lkii1#
The original code from the OP truncates to the hour. It does NOT round to the nearest hour. It also does not "GROUP BY the Date and Hour", as the title suggests. It only does the "truncation" to the hour.
With that in mind, here are some different solutions for SQL Server 2005 and up.
Note1: In the following, GETDATE() could be a column name from a table if the query reads from a table.
Note2: If the desired resolution is whole days, be cautious about just using CONVERT(DATE), which could cause a datatype mismatch.
Note3: Always watch your datatypes if such conversions are used for criteria.