Preserve H:M:SS - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Preserve H:M:SS

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-11-24 : 03:48:23

I am querying 2 DateTime columns from a table. The columns are 'LogOnTime' and 'LogOffTime'. If 'LogOnTime' has a value of '03/10/2006 13:52:18' and 'LogOffTime' has a value of '03/10/2006 20:31:17' and the ID for that row is 4, and I run the following query:


SELECT SUM(datediff(hour,LogOnTime,LogOffTime)) as [Total Login Time]
FROM ActionSession
WHERE SessionID = 4

It returns 7

While I can see why this is, it is not what I want. I need to return a figure that, based on the above, would be 6.39.59 (if my calculation is correct) giving me the full hours, minutes and seconds difference between the 2 columns.

Is this possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 03:57:13

SELECT SUM(datediff(second,LogOnTime,LogOffTime)) / 3600.0 as [Total Login Time]
FROM ActionSession
WHERE SessionID = 4

or

SELECT SUM(datediff(minute,LogOnTime,LogOffTime)) / 60.0 as [Total Login Time]
FROM ActionSession
WHERE SessionID = 4

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 04:04:06

[code]declare @t table
(
LogOnTime datetime,
LogOffTime datetime
)

insert @t
select '03/10/2006 13:52:18', '03/10/2006 20:31:17'

select cast(((datediff(second, LogOnTime, LogOffTime)/60)/60)%60 as varchar(2)) + '.' + cast((datediff(second, LogOnTime, LogOffTime)/60)%60 as varchar(2)) + '.' + cast(datediff(second, LogOnTime, LogOffTime)%60 as varchar(2)) as [Total Login Time]
from @t[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 04:16:07

If you want the minutes and seconds with leading zero (for minute and second less than 10), try this

--prepare test data
declare @t table (LogOnTime datetime, LogOffTime datetime)

insert	@t
select	'03/10/2006 13:52:18', '03/10/2006 20:31:17' 

-- do the work
select	ltrim(j.h) + '.' + replace(j.m, ' ', '0') + '.' + replace(j.s, ' ', '0') [Total Login Time]
from	(
		select	str(datediff(second, LogOnTime, LogOffTime) / 3600, 30, 0) h,
			str(datediff(second, LogOnTime, LogOffTime) % 3600 / 60, 2, 0) m,
			str(datediff(second, LogOnTime, LogOffTime) % 60, 2, 0) s
		from	@t
	) j

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 04:44:16

I think the method you posted before editing was much elegant.

Why did you erased that? It was just working fine! Also, it didn't required string concatenation stuff.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-24 : 04:48:28

It'll be in Peso's Version Control store ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 04:49:22

I edited it because it had a leading zero for hours. Also, if sum of time difference is greater than 24 hours the result will start over from 00:00:00

--prepare test data
declare @t table (LogOnTime datetime, LogOffTime datetime)

insert	@t
select	'03/10/2006 13:52:18', '03/10/2006 20:31:17' 

-- do the work
select	convert(varchar, dateadd(second, datediff(second, logontime, logofftime), 0), 108) [Total Login Time]
from	@t

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-24 : 10:08:38

No one ever suggests this method for elapsed time. I can't understand why, since it seems so much simpler.


declare @t table (Login datetime, Logout datetime)

insert	@t
select	'20061022 11:33:22', '20061022 21:46:37' 

select	
	[Login Time] =
	convert(varchar(8), Logout - Login, 108 )
from	@t

Results:


(1 row(s) affected)

Login Time 
---------- 
10:13:15

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources