Time Online - 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)

Time Online

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-11-23 : 10:21:57

I have a table that records when a user is logged onto our system. I want to produce a report that lists the users currently online and how long they gave been online.

I have used the code getDate() - LoginDate to get a rough idea. This is not ideal though, as if a user has been logged in for 6 minutes I get 1900-01-01 00:6:07.743

Is there anyway I can get something along these lines.

User TimeOnline
---------------------------------------
User1 1 hour 6 Minutes
User2 3 Minutes
User3 1 day 4 hours 0 minutes

Thanks

Leah

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-23 : 10:37:09

you already have your answer -- you just need to display it the way you'd like.

datetime values must have both components -- a date AND a time. So, some date must be attached to all values of that datatype. but you can always ignore the date and just display or use the time portion.

so, just do your math -- GetDate() - LogOnTime -- and on the results format them as needed. Take parts of the value returned, and adjust as necessary to make 1/1/1900 equal to "0 years, 0 months, 0 days", like this:



-- THIS IS SAMPLE DATA FOR YOU
declare @t table (UserID int, LogOnTime datetime)

insert into @t
select 1,'11/23/2004 3:00 AM' union
select 2,'8/22/2004 7:00 PM' union
select 3,'1/20/2004 10:12 AM' union
select 4,'11/23/2003 8:53 AM' union
select 5,'11/21/2004 11:59 PM'

-- HERE IS A SAMPLE SQL STATEMENT 

select userID, LogOnTime,
        Year(TimeOn)-1900 as Years,
	Month(TimeOn)-1 as Months,
	Day(TimeOn)-1 as Days,
	DatePart(hh,TimeOn) as Hours,
	DatePart(mi,TimeOn) as Minutes
from
    (   
	
	select *, GetDate() - LogOnTime as TimeOn
	from @t
    ) a

Does this help?

- Jeff

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-11-23 : 11:21:16

Thanks, that helps a lot. I could just not get my head alround it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources