Click here to Skip to main content
15,851,711 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
SQL
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
Updated 11-Sep-12 3:10am
v2

Hi,
Try this code to convert the DateTime to UTC DateTime.
SQL
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
 
Share this answer
 
You mean like this
select DATEDIFF(SECOND,@LocalDate,@UTCDate)

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

SQL
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
 
Share this answer
 
v2
Comments
Maciej Los 14-Sep-12 16:05pm    
This is not an answer! Please, use "Have a question or comment" widget.
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.
 
Share this answer
 
v2
Comments
Maciej Los 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900