SQL Server Group by date and hour

u7up0aaq  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(76)

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'
fhg3lkii

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.

--===== A solution for all versions of SQL Server for dates >= 1900-01-01.
     -- This will will work for minutes as well as hours provided that 
     -- you don't need a date result > 5983-01-24 02:07. Also, it should
     -- NOT be used for WEEK nor can it be used ISOWEEK.
 SELECT DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0)
;
--===== Solution for all versions of SQL Server for dates >= 1753-01-01,
     -- which is the earliest date possible for the DATETIME datatype.
     -- This will will work for minutes as well as hours provided that 
     -- you don't need a date result > 5836-01-25 02:07. Also, it should
     -- NOT be used for WEEK nor can it be used ISOWEEK.
 SELECT DATEADD(hh,DATEDIFF(hh,'1753',GETDATE()),'1753')
;
--===== Slightly faster version of the solution immediately above but
     -- less obvious to newbies and neophytes.
 SELECT DATEADD(hh,DATEDIFF(hh,-53690,GETDATE()),-53690)
;
--===== The following solution works for all possible dates in SQL Server
     -- starting at 0001-01-01 (Inherently, SQL Server 2008 and up)
     -- This will will work for minutes as well as hours provided that 
     -- you don't need a date result > 4084-01-24 02:07. Also, it should
     -- NOT be used for WEEK nor can it be used ISOWEEK.
 SELECT DATEADD(hh,DATEDIFF(hh,'00010101',GETDATE()),CONVERT(DATETIME2,'00010101'))
;
--===== Solution for SQL Server 2022 and up
     -- This will work for any temporal resolution available for
     -- any date/time possible in SQL Server including WEEK and ISOWEEK.
     -- Unlike DATEDIFF, this also follows DATEFIRST for the WEEK datepart.
 SELECT DATETRUNC(hh,GETDATE())
;

相关问题