Average of time of a datetime field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Average of time of a datetime field

Author

Topic

softengilker
Starting Member

2 Posts

Posted - 2006-09-19 : 06:05:11

Hi all;
I want to get the average of time of a datetime field in the table as hour and minute.

I have tried this:
select avg( convert( char( 8 ), fieldX, 108 ) ) from tableX.

But it is clear that the parameter in the avg function is a char. I can not take the hour and minute part from the datetime field. datepart does not work, because you can get the hour or the minute, not both.

Can please help me?

They were successful. Because They did not know achieving the task had been impossible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 06:28:21

AVG does not work on VARCHAR.

Try this, if you want AVG down to seconds.

SELECT	CONVERT(VARCHAR, DATEADD(second, AVG(DATEDIFF(second, 0, ColumnX - DATEADD(day, DATEDIFF(day, 0, ColumnX), 0))), 0), 108)
from	TableX

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources