SQL Query - Average time from datetime field

Question

I'm struggling with what I thought would be a simple SQL query. Running SQL Server 2014

I have an SQL table, "Visits":

Id | EntryTime | Duration

And I want to find the average entry TIME OF DAY between two dates, taking into account all records between those dates.

so if my EntryTime field between my dates is:

2016-04-28 12:00:00
2016-04-20 10:00:00
2016-04-19 08:00:00
2016-04-17 10:00:00

Then the average time returned should just be:

10:00:00

The date should not be taken into account at all, and it should be returned in string format, or a manner which returns ONLY 10:00:00.

Answer 1

create table mytimes(
   id int identity,
   mydatetime datetime
)

insert into mytimes (mydatetime) values ('2016-04-28 12:00:00')
insert into mytimes (mydatetime) values ('2016-04-20 10:00:00')
insert into mytimes (mydatetime) values ('2016-04-19 08:00:00')
insert into mytimes (mydatetime) values ('2016-04-17 10:00:00')

SELECT Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time ) 
from mytimes
-- where mydatetime between XXX and YYY

SELECT convert(varchar(8), Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time )) 
from mytimes
-- where mydatetime between XXX and YYY

output-1 10:00:00.0000000 - this is an actual Time type that you can do more with if needed

output-2 10:00:00 - this is output as a varchar(8)

Add your where clause as you see fit

The steps include

• Casting to a Time type from a DateTime.
• Using the AVG on Time, this is not supported by type Time so you have to first convert Time to milliseconds.
• Converting the milliseconds back to a Time type
• To avoid Arithmetic overflow error converting expression to data type int you can cast the result of DateAdd to a BigInt. Alternatively you can use seconds instead of milliseconds in the DateDiff function which should work unless your result set is overly large.

SO Sources:

• T-SQL calculating average time
• How to get Time from DateTime format in SQL?
• Operand data type time is invalid for avg operator…?

Answer 2

SELECT CONVERT(TIME, DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(TIME, EntryTime ))), 0)) 
FROM Visits
WHERE EntryTime >= @begin AND EntryTime <= @end 

The idea came from here: T-SQL calculating average time