Grouping SQL Server Data by Time with DATEPART or DATENAME

This article is the first in a series of three that demonstate various ways to slice data by time band. The following two articles will show how to group SQL Server by date/time with DATEDIFF and DATEADD and how to group data by date/time with a temp table.

The example below shows how to roll up data in SQL Server by time band using the built-in DATEPART or DATENAME functions. You will want to consider a variation of this approach when rolling data up by calendar units.

A deficiency of this method is that, because it is based solely on the underlying data, it can only return groups for time bands in which which you have data. In other words, say you want to display a graph showing the last year's results, but only have data for the last three quarters, your graph would begin in April rather than January. I will address this problem in the final article of this series.

The DATEPART and DATENAME functions both take as thier first parameter one of the following constants that determines the part of the date extracted: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond. The difference between DATEPART and DATENAME is that DATEPART returns an integer representing the specified part and DATENAME returns a string. For instance:

SELECT DATEPART(month, '2009-01-01 00:00:00:000')

would return "1" as an integer, whereas:

SELECT DATENAME(month, '2009-01-01 00:00:00:000')

would return "January".

Now on to the example: If you run the following code in a query window, you will receive 12 bands of data. The counts should correspond to the number of days in the respective months since there is one data point per day. 

--These values would most likely be
--params passed in. Here, we just
--fake them for illustration purposes.
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2009-01-01 00:00:00:000'
SET @EndDate = '2010-01-01 00:00:00:000'
 
--Declare a table for the example.
DECLARE @MyData table
(
      DateField datetime,
      CountField int
)
 
--Fill the table with fake data. CountField
--is set to one per day to make it easy to
--tell if query is working properly.
DECLARE @Day int
SET @Day = 0;
WHILE (@Day < 365)
BEGIN
      INSERT INTO @MyData
            SELECT DATEADD(day, @Day, @StartDate), 1;
      SET @Day = @Day + 1;
END
 
--Get the data rolled up by month.
SELECT
      DATENAME(month, md.DateField) AS SliceName,
      DATEPART(month, md.DateField) AS SliceNumber,
      SUM(md.CountField) AS CountForSlice
FROM
      @MyData md
WHERE
      md.DateField BETWEEN @StartDate AND @EndDate
GROUP BY
      DATENAME(month, md.DateField),
      DATEPART(month, md.DateField)
ORDER BY
      DATEPART(month, md.DateField)

Applies to: SQL Server 2005/2008