Aggregating SQL Server 2008 Date and Time Values

As I covered in a recent video outlining the new Date and Time Data Type improvements to T-SQL 2008, T-SQL now has distinct Date and Time data types. And given that the Time Data Type only tracks time, you might be inclined to think that the following would work as a way to get the sum of all times – because, after all… Time is the only value being tracked:

DECLARE @Times TABLE (
	TimeValue time NOT NULL
)
INSERT INTO @Times (TimeValue)
VALUES ('00:12:00'), ('00:22:18'),('00:03:49')

SELECT SUM(TimeValue) FROM @Times

So, it's sort of natural to just assume that SQL Server will add up all the time values in the temp-table and return their SUM as '00:38:07'.

Only, if you try running such a query, you'll get an error:

Msg 8117, Level 16, State 1, Line 8
Operand data type time is invalid for sum operator.

That, of course, is because you're throwing SQL Server for a loop by not being specific with what you want aggregated. Do you want to know the total number of hours? minutes? seconds? milliseconds? nano-seconds?

Accordingly, if you want to aggregate time, you need to specify exactly what units you wish to aggregate. So, the following would work:

SELECT SUM(DATEPART(n,TimeValue)) FROM @Times

But it, of course, only tracks minutes. So, if you're interested in other DATEPARTs you'd have to account for those independently as well – and the sticky part comes when you need to 'carry' over values from one time segment to its parent and so on. (Such as in the case when 67 seconds needs to be converted to 1 minute and 7 seconds.) As such, this is why you'll typically see these kinds of aggregations done from with applications or within UDFs…