Click here to Skip to main content
12,246,406 members (45,974 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
I need to find the users who are online in a website.

For that I need to convert the lastlogintime which is in 24 hrs to UTC time

Then I need to get differnce between the cuurentutctime and the lastlogintime

If diff is < 20 mts, user is online. How is this done in sql server.
SELECT MU.Id, U.UserId, U.UserName, U.FirstName, U.LastName, U.LastLoginTime, 
 
 önlineStatus =
CASE
When 
DATEDIFF(SECOND,GETUTCDATE(),dateadd(SECOND,datediff(SECOND,getutcdate(),GETDATE()),(CONVERT(varchar(25),U.LastLoginTime,100))))<900 
then 'Online'
ELSE 'Offline' 
END 
FROM MembershipUser MU INNER JOIN User U
ON MU.UserId = U.UserId

I tried with this. But the issue is getdate() is in 12 hours format and LastLoginTime is in 24 hours.

Can anyone pls help. I don't know how to make these in same format
Posted 11-Sep-12 3:07am
Edited 11-Sep-12 3:10am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,
Try this code to convert the DateTime to UTC DateTime.
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()
 
-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)
 
SELECT @LocalDate, @UTCDate
Now find the use the DATEDIFF function to calculate the time and get your solution.


Hope it works for you.
--Amit
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You mean like this
select DATEDIFF(SECOND,@LocalDate,@UTCDate)

It would be really helpful if you pls incoporate your logic to my query
ie

SELECT MU.Id, U.UserId, U.UserName, U.FirstName, U.LastName, U.LastLoginTime,
 
 önlineStatus =
CASE
When
DATEDIFF(SECOND,GETUTCDATE(),dateadd(SECOND,datediff(SECOND,getutcdate(),GETDATE()),(CONVERT(varchar(25),U.LastLoginTime,100))))<900
then 'Online'
ELSE 'Offline'
END
FROM MembershipUser MU INNER JOIN User U
ON MU.UserId = U.UserId
  Permalink  
v2
Comments
losmac 14-Sep-12 16:05pm
   
This is not an answer! Please, use "Have a question or comment" widget.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi,
Can you pls tell how I can include this in my query.Also, you have set localtime to getdate(). My datecolumn is in 24 hour format, but getdate() is in 12 hour format.
  Permalink  
v2
Comments
losmac 14-Sep-12 16:06pm
   
This is not an answer! Please, use "Have a question or comment" widget.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160426.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100