Group by datetime ignoring time portion

Question

Is it possible to write a Microsoft SQL query that will group by a datetime data-type but ignoring the time part such as the hour and minute?

Answer 1

If you are on SQL Server 2008 this is simple.

GROUP BY CAST(YourCol AS Date)

For previous versions you can use

GROUP BY DATEDIFF(DAY, 0, YourCol)

However neither of these will be able to leverage the fact that an index on YourCol that is ordered by datetime will also be ordered by date and thus use a stream aggregate without a sort operation.

On SQL Server 2008+ you might consider indexing (date,time) rather than datetime to facilitate this type of query.

Either by simply storing it as two separate components and possibly providing a calculated column that recombines the parts (a datetime2 is stored the same as a date and a time so this won't consume any more space except for if the additional column pushes the NULL_BITMAP onto a new byte.).

CREATE TABLE T
(
YourDateCol date,
YourTimeCol time,
YourDateTimeCol AS  DATEADD(day, 
                            DATEDIFF(DAY,0,YourDateCol), 
                            CAST(YourTimeCol AS DATETIME2(7)))
/*Other Columns*/
)

Or alternatively you could store it combined in the base table and have the indexes use calculated columns that split it out.

An example of this approach

CREATE TABLE T
(
DT DATETIME2,
D AS CAST(DT AS DATE),
T AS CAST(DT AS TIME)
)

CREATE INDEX IX1 ON T(DT);

SELECT COUNT(*), 
       CAST(DT AS DATE)
FROM T
GROUP BY CAST(DT AS DATE)

CREATE INDEX IX2 ON T(D,T);

SELECT COUNT(*), 
       CAST(DT AS DATE)
FROM T
GROUP BY CAST(DT AS DATE)

DROP TABLE T

Answer 2

If you have a large table and want grouping to happen fast, you cannot rely grouping on an expression.

Create an additional datetime column on your table and have it filled by a trigger that calculates the base date from you "real" date:

UPDATE
  MyTable
SET
  EffectiveDate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t.RecordDate))
FROM
  MyTable t
  INNER JOIN inserted i ON i.RowID = t.RowID

Then put an index on EffectiveDate and grouping (and searching) will be a lot faster.