Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to calculate Average datetime in sql server Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 9:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
DECLARE @date1 DATETIME,@date2 DATETIME,@date3 DATETIME;
SET @date1=CONVERT(varchar,GETDATE()+1,108)
SET @date2=CONVERT(varchar,GETDATE()+2,108)
SET @date3=CONVERT(varchar,GETDATE()+3,108)

SELECT * INTO #temp
FROM(
SELECT @date1 AS date
UNION
SELECT @date2 AS date
UNION
SELECT @date3 AS date) AS p


Can any one send how to calculate the average of datetime in sql server 2005 and avg function is not working and i have no permission to create cursor or functions in a database i only able to create temp tables.


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #870896
Posted Tuesday, February 23, 2010 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:30 AM
Points: 5,019, Visits: 10,554
Convert the date column to FLOAT, then back to DATETIME:

DECLARE @test TABLE (
dtColumn datetime
)

INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())


SELECT CAST(AVG(CAST(dtColumn AS FLOAT)) AS datetime)
FROM @test

Hope this helps
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #870980
Posted Tuesday, February 23, 2010 5:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Hi thanks for reply,

this above code is fine but it will take the average time not full datetime.

I want to calculate time not average of date.



Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #871057
Posted Tuesday, February 23, 2010 6:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:30 AM
Points: 5,019, Visits: 10,554
Just the time part? OK, take the fractional part only:

DECLARE @test TABLE (
dtColumn datetime
)

INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())
INSERT INTO @test VALUES (GETDATE() + RAND())


SELECT CONVERT(char(8),CAST(AVG(CAST(dtColumn AS FLOAT) - FLOOR(CAST(dtColumn AS FLOAT))) AS datetime),108)
FROM @test



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #871077
Posted Tuesday, February 23, 2010 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
ok.Its working fine

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #871087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse