How to convert datetime value to UTC format

Question

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 11-Sep-12 2:07am

Member 8996182

Updated 11-Sep-12 2:10am

Kenneth Haugland

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 8996182 · Answer 1 · 2012-09-11T06:51:00

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.

Posted 11-Sep-12 6:51am

Member 8996182

Updated 11-Sep-12 6:58am

v2

Comments

Maciej Los 14-Sep-12 16:06pm

This is not an answer! Please, use "Have a question or comment" widget.

Member 8996182 · Answer 2 · 2012-09-11T02:31:00

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

_Amy · Answer 3 · 2012-09-11T02:15:00

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

How to convert datetime value to UTC format

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0