Average and Operator - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Average and Operator

Author

Topic

Shelly1
Starting Member

25 Posts

Posted - 2010-01-07 : 08:00:19

Hi all,

The following syntax returns the TimeAlloc as HH:MM:SS

I would like to turn the time that it returns into an Average, however i am not having any joy.

convert(varchar(8),TimeAlloc,14)TimeAlloc,

I did try:

AVG(convert(varchar(8),TimeAlloc,14)) AVGTimeAlloc,

This returns the following error message:

Operand data type varchar is invalid for avg operator.

Can anyone help at all?

Thanks

Mich

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-07 : 08:05:29

What datatype is TimeAlloc column?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-07 : 08:11:10

[code]DECLARE @Sample TABLE
(
TimeAlloc DATETIME,
TimeSeconds INT
)

INSERT @Sample
SELECT '02:03:00', 7380 UNION ALL
SELECT '00:45:30', 2730

SELECT CONVERT(CHAR(8), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, DATEADD(DAY, DATEDIFF(DAY, TimeAlloc, 0), TimeAlloc))), 0), 114),
CONVERT(CHAR(8), DATEADD(SECOND, AVG(TimeSeconds), 0), 114)
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 09:21:21

SELECT CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,0,TimeAlloc)),0),108)
FROM Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources