This fellow has a blog, and it’s good: Grouping datetime by date in T-SQL « SQL Disco. Many times in SQL you need to group by year, or by date, and what you have to work with is a big nasty DATETIME field. I needed to find out how many updates had been done each year, and I had gnat’s ass quality timestamps, down to the second.
I know what you’re thinking, but if you use CONVERT(dtgMyDate), you’re doing it wrong, as our friend at SQL DISCO points out. In his example, he groups by day; in this one, I have modified it to group by year:
SELECT DATEADD(yyyy,(DATEDIFF(yyyy,0,dtgMyDate)),0),
COUNT(*)
FROM Table1 GROUP BY DATEADD(yyyy,(DATEDIFF(yyyy,0,dtgMyDate)),0)
You see what he does there? Instead of costly CONVERT, CAST, or other time/power wasting (God forbid, parsing for text and casting back to numbers), he leverages the fact that it’s already a date, and the machine already understands it as a date, and a date ain’t nothin’ but a number to a computer (okay, an offset from an agreed-upon date, but let’s keep this friendly). Same for time.
All he’s doing there is taking the DATETIME, subtracting zero from it but critically in units of years (the DATEDIFF) and then adding that many years back to zero (the DATEADD). Finally, because it was already understood as a DATETIME, it still is, but we have set everything except the year to zero (or one, as necessary). The result is not 2009, but 1/1/2009 12:00:00. This means that we may now group on it and everything in that year will show up with the same month, day, hour, minute, and second. If we wanted to, we could format the output to show just a year, but leave that for the reporting–I work in the aggregation department.
