Average time duration ? 1

[redgate]

Hi

As an input file I have a list of times(durations) in the format hh:mm:ss .

I created a table with a column of datetime and have written these in the format '1900/01/01 hh:mm:ss'.

I am trying to get an avearge of these durations.
I would also like to calculate the total in days, hours and minutes.

I suspect that maybe my column datatype is the incorrect choice.

Am I way off track ?

 

[mwolf00]

Does this help?

SELECT cast(avg(cast(duration as integer))as datetime) from myTable

-- Just trying to help... LOL

 

[redgate]

Hi mwolf00

I tried your code but it returns '1900/01/01 00:00:00' .

I then tried simply

SELECT cast(duration as integer) from myTable

and my results where all zeroes.

Thanks for the reply.

Any other thoughts ?

 

[mwolf00]

OOOHHHH! Sorry....

SELECT cast(avg(cast(duration as float))as datetime) from myTable -- Just trying to help... LOL

 

[redgate]

Hi mwolf00

So far I have got this to work

DECLARE @NVAL DATETIME
DECLARE @DIFF INT

SET @DIFF = (SELECT AVG(DATEDIFF(S, '1900/01/01',DURATION)) FROM MYTABLE)
SET @NVAL = DATEADD(S, @DIFF,'1900/01/01')

SELECT @NVAL AS AVERAGETIME

but can't help thinking I am going in the wrong direction.
Will try your next suggestion.

Thanks again.