Determine how much time passed within result set? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Determine how much time passed within result set?

Author

Topic

johnvm
Starting Member

18 Posts

Posted - 2007-12-12 : 17:09:06

Hey Guys,

I got a table with a column like this:

2007-12-12 15:39:21.000
2007-12-12 15:39:23.000
2007-12-12 15:39:25.000
2007-12-12 15:39:28.000
2007-12-12 15:39:30.000
2007-12-12 15:39:48.000
2007-12-12 15:39:54.000
2007-12-12 15:40:12.000
2007-12-12 15:40:15.000
2007-12-12 15:40:24.000

I want to write a query that essentially does the last time there minus the first time there -- tell me how much time has passed between the timestamp in the last row and the first row (so in this case it'd return "63 seconds", or a datetime value of '00:01:03' or something).

How could I go about writing such a query?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 17:13:58

SELECT DATEDIFF(s, MIN(YourColumn), MAX(YourColumn))
FROM YourTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 17:15:37

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '2007-12-12 15:39:21' UNION ALL
SELECT '2007-12-12 15:39:23' UNION ALL
SELECT '2007-12-12 15:39:25' UNION ALL
SELECT '2007-12-12 15:39:28' UNION ALL
SELECT '2007-12-12 15:39:30' UNION ALL
SELECT '2007-12-12 15:39:48' UNION ALL
SELECT '2007-12-12 15:39:54' UNION ALL
SELECT '2007-12-12 15:40:12' UNION ALL
SELECT '2007-12-12 15:40:15' UNION ALL
SELECT '2007-12-12 15:40:24'

SELECT DATEDIFF(SECOND, MIN(dt), MAX(dt)) AS Diff,
CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, MIN(dt), MAX(dt)), '19000101'), 108) AS Yak
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

johnvm
Starting Member

18 Posts

Posted - 2007-12-12 : 17:25:09

You guys rock. Thanks a billion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources