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: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 4,654, Visits: 11,115
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #870980
Posted Tuesday, February 23, 2010 5:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 4,654, Visits: 11,115
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #871077
Posted Tuesday, February 23, 2010 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
ok.Its working fine

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

Add to briefcase

Permissions Expand / Collapse