I often find myself grouping data by various time periods. Usually, I use day/month/year. If you’ve played around with datemath at all, it can get rather complicated quickly. Early on, I found this article which really resonated with me.

I use SSMSTools SQL Snippets to assign groupbyday[tab] to automatically populate this:

dateadd(DAY, datediff(DAY, 0, {C}),0)

I replace the {C} with the field I’m interested in grouping by or rounding off, and replace “DAY” with the period I’m interested in.

SELECT dateadd(DAY, datediff(DAY, 0, o.OrderDate),0) AS OrderDay, count(*), SUM(o.OrderTotal) AS OrderTotal

FROM Orders o

GROUP BY dateadd(DAY, datediff(DAY, 0, o.OrderDate),0)

This also allows me to +/- days/months easily by adding or subtracting from the 2nd parameter in the outside function. The following shows all of yesterday’s orders.

DECLARE @StartDate datetime2;

SET @StartDate = dateadd(DAY, datediff(DAY, 0, getdate())-1,0)

DECLARE @EndDate datetime2;

SET @EndDate = dateadd(DAY, datediff(DAY, 0, getdate()),0)

SELECT SUM(o.OrderTotal) AS OrderTotal

FROM Orders o

WHERE o.OrderDate >= @StartDate AND o.OrderDate < @EndDate

I could push the logic to the left side (e.g. WHERE dateadd(DAY, datediff(DAY, 0, o.OrderDate),0) = dateadd(d,-1,getdate())), but that effectively forces SQL Server to skip any indexes it had on OrderDate. Here’s some good reading on using dates in SQL Server.

Do you have any good tips on handling dates in SQL? Let me know in the comments.