The SQL TIME data type is handy for adding times to other times. It does; however, have a limitation in that the highest time it will store is 23:59:59.9999999. This is great if you are adding dates and times simultaneously as the date will increase when you hit 24 hours.
The flaw comes in if you are only adding time (such as minutes) and you are trying to sum the total time for something that adds up to longer than than 24 hours. For example, lets say you have 1,439 employees and each one takes 1 minute to complete a task. When you add the times together, everything is fine. Your total time for all employees to complete the tasks is 23 hours and 59 minutes.
declare @emptime as Time
declare @empid as int
set @emptime = '00:00:00'
set @empid = 1
while @empid <= ‘1439’
Begin
set @emptime = dateadd(minute,1,@emptime)
set @empid = @empid + 1
End
select @emptime
–Result: 23:59:00.0000000
The problem comes when you add one more employee. Instead of the time totaling to 24 hours, it rolls over and resets to zero.
declare @emptime as Time
declare @empid as int
set @emptime = '00:00:00'
set @empid = 1
while @empid <= ‘1440’
Begin
set @emptime = dateadd(minute,1,@emptime)
set @empid = @empid + 1
End
select @emptime
–Result: 00:00:00.0000000
Because of the way the time field is designed, you cannot use it field to add all the times together. Each time you hit 24 hours, you will roll back to zero.